Need to create database using other windows authentication username and password

Question

0.00/5 (No votes)

See more:

Hello,

Currently I have connected database using windows authentication (default allow my windows creds). Ex: Server/User1
I need to create database using other windows credentials. Ex: Server/User2
The new database DBO rights should be Server/User2

Can help to achieve this scenario.

Let me know the SQL Query on this scenario.

Scenario explanation below:-
Step1:
Customer login system using them own user name and password (Ex:User Name: System\User1).
Step2:
Running one of the C# windows application exe. Application showing the DB Servername, DB Name, Login, Password.
Step3:
Enter Database server name Ex: (local)\SQLExpress
Enter Database name Ex: MyDatabase
Enter Database user name Ex: System\DBAdmin
Enter Database password Ex: **********
Note: System\DBAdmin already added localhost SQL Login (Security -> Login) with Sysadmin permission.
Step4:
Then successfully it should to create database for the login (System\DBAdmin)

Below should be expected after created database:-
1. Go to the SQL Sever Management Studio
2. Connect (local)\SQLExpress & Windows Authentication
3. Expand Databases -> MyDatabase should be created
4. Click MyDatabase and check below things:-
MyDatabase -> Security -> Users -> dbo -> General -> User type -> Windows User
MyDatabase -> Security -> Users -> dbo -> General -> Login name -> System\DBAdmin

Regards,

Vasanth

What I have tried:

I have added user under Login, tried to create database but no luck.

Posted 21-May-20 6:52am

vasanthkumarmk

Updated 30-May-20 3:23am

v2

Add a Solution

Comments

ZurdoDev 21-May-20 12:55pm

This is not a code issue so you may get better results in a technical support forum, but all you need to do is create a new login and password and assign to the db. You can do this in Sql Server Management Studio under the Security folder.

vasanthkumarmk 21-May-20 12:59pm

I need to do from query. If I logout my user (User1) and login to another user (User2) I can able to create database and DB right also corresponding user.
But my case connected windows authentication in User1, but need to create database for User2.

phil.o 21-May-20 14:13pm

Credentials are managed at the connection level, not at the query level. You cannot issue a query to change the login of a connection.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2020-05-22T04:20:00

Solution 1

Maybe the answer here will be helpful:
Connect to SQL Server with Windows Authentication in a different domain - Database Administrators Stack Exchange[^]

Posted 22-May-20 4:20am

RickZeeland

Comments

vasanthkumarmk 25-May-20 1:45am

Thanks... I am looking to create DB using query. We can do create database use run as different user in SSMS.

CHill60 · Answer 2 · 2020-05-22T04:27:00

Solution 2

It sounds like you are trying to do this CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL) - SQL Server | Microsoft Docs[^] or CREATE CREDENTIAL (Transact-SQL) - SQL Server | Microsoft Docs[^]

The only way to create a database "using" the other windows credentials is to be logged on using those credentials. I suspect it's just a terminology problem and you are trying to change the owner of a database - have a look at How to Change Owner of Database in SQL SERVER? [^]

Posted 22-May-20 4:27am

CHill60

Comments

vasanthkumarmk 25-May-20 1:44am

Thanks but no luck.

CHill60 26-May-20 6:25am

Instead of just saying "no luck" can you tell us specifically what you did and what happened?

vasanthkumarmk 27-May-20 2:27am

USE master
GO
\\SERVER\Login1 is my windows default login account
GRANT IMPERSONATE ON LOGIN:: [SERVER\Login1] TO [SERVER\Login2]
GO
CREATE DATABASE TESTING9
GO

I would like to expect create database TESTING9 for using login account [SERVER\Login2]. So TESTING9 -> Security -> Users -> dbo -> Login name -> should be '[SERVER\Login2]'

CHill60 27-May-20 6:09am

I'm not sure why you would expect create database to do all of that without the additional steps to change the owner... try

ALTER AUTHORIZATION ON DATABASE::TESTING9 TO Login2;

vasanthkumarmk 27-May-20 10:14am

Sorry for confusion. Below I explain what customer expecting:-
Subject: Create (Windows Authentication) database using others Active directory login.
Explanation: Imagine employees using multiple systems in a office, those have single DB Administrator, DB Admin have only rights to install database for all employees in the office. So DB Admin has already added particular permissions for all employees machines (Security -> Logins -> Server/DBadmin) (also added Server Roles permission as sysadmin).
Then DB Admin has given one particular windows active directory user id (Server/DBadmin) and password (******) for all employees to install database. Employees using one c# windows application providing the DB Admin user name, password, Servername, database name then it will create (Windows Authentication) database. So once created database, then database should be TESTING9 -> Security -> Users -> dbo -> Login name -> should be '[Server/DBadmin]'

---- This is my expectation------

I am trying impersonate user before create database using SQL Query. But unable to achieve.

CHill60 27-May-20 12:17pm

So change the ownership of the database after it has been created. You cannot "use others Active directory login" unless you mean you are trying to do EXECUTE AS (Transact-SQL) - SQL Server | Microsoft Docs[^].

It also sounds as if you are creating lots of databases on each user's machine - that is a strange way to go about things - most applications connect to a central database, unless running disconnected.

But in summary - once the database has been created you can set any login to be the owner - just run the sql command I gave you earlier (alter authorization …) with the appropriate database name and the login that you want to be the owner

vasanthkumarmk 28-May-20 1:29am

Thank you so much for your suggestion.

But as per my requirement I could not able to create database in current user login. Only other Active directory login only I should create database.
So changing ownership is not possible. Can you pleaseee help me using EXECUTE AS to create database for others login.

It is connected in central database only.

Once again thank you so much your reply. I have been trying on the way you suggesting.
Please keep on help... Thanks!!!!!

vasanthkumarmk 28-May-20 1:33am

USE master
GO
SELECT SUSER_NAME() AS LogInName,
USER_NAME () AS UserName
GO
EXECUTE AS LOGIN = 'SERVER\User2'
GO
CREATE DATABASE ImpersonateDB
GO
SELECT SUSER_NAME() AS LogInName,
USER_NAME () AS UserName

Error = Could not obtain information about Windows NT group/user 'SERVER\User2', error code 0x5.

CHill60 28-May-20 3:58am

Have I got this right?
So you are creating a central database - everybody else will connect to that one database.
Your login cannot create the database but you know the details of a logon that can.
Why not connect to SQL server with the logon that can create the database and just create it?

You really need to describe the scenario step by step and don't use "I" in the description - use the logon names. And it would be better if you use the "Improve Question" link so that more people can see the details without have to trail through all these comments

vasanthkumarmk 30-May-20 9:22am

Thanks.....Edited and improved question

CHill60 30-May-20 10:58am

That's a good description. I'm on my phone at the moment so can't really look into it just now, but hopefully other members will see the amendment and step in to help. I'll come back when I can

vasanthkumarmk 31-May-20 8:34am

Sure... Thank you so much.... Happy for your response....

Need to create database using other windows authentication username and password

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Need to create database using other windows authentication username and password

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us