Can't create user in SQL Server

Question

Hi guys when I try to create user for my database I always encounter this kind of error.

This is the sequence of task I done.

I manage to create new login using windows authentication of SQL then I use that login to access SQL Server again then I create my database and set the owner of that database to the login that I was created. After creating the database, I try to create new user and assign the login I use but I was halted by this error.

Answer 1

If you made that user the database owner, then he already is the dbo user. So the dbo user is associated with that login already like the error message says.

Answer 2

It is normal you cannot associate two different user to a single login for a database. You must understand the difference between a login and a user.

A login authenticate you on the server. Once you are authenticated, the login will impersonate a user to access the specified database of the connection string.

So a login can have only one user per database.

Sample : login [myDomain\myUser] has access on SqlServer. This login can have a user per database : userA for databaseA, userB for databaseB.

If you want to add an other user to the login for the same database, you have to drop the first user. Instead, you have to create a different login for the second user.

Hope this help you.

Answer 3

I ran into this just now with SQLSERVER 2012 (or actually SQLEXPRESS but I think the principle's the same). I created a connection string and attached an .MDF file to my Visual Studio C# web application project. When I try to run the solution, I get this error:

Cannot open database "DB" requested by the login. The login failed. Login failed for user 'PC-NAME\'My windows login'

I'm not that concerned about security right now, this is just a prototype, so I simply want to use my everyday Windows credentials to access the "server" (actually the same notebook), and then log into the database specific to my application.

So having seen the above error message I fire up SSMS and make sure 'PC-NAME\My windows login' is listed among the logins. It is indeed listed, with pretty much every possible role and privilege. I check the application database "DB". Aha! there's no such user, or so it seems. Yet when I try to add user 'PC-NAME\My windows login' to the app database, I now get this error:

The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)

So now what? I think the app database user 'dbo' is the user in question, but I can't delete that users because, apparently, it's also a schema for the database.

Answer 4

Extending my comment.

You should be able to set database owner during creation of database in SQL Server Management Studio- check Owner field located under Database name field.

Or you could use sp_changedbowner (http://msdn.microsoft.com/en-us/library/ms178630.aspx) to change database owner to other login then one for which you would like to create a new user.