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.
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.
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.
Are you doing this to learn or for a real project? I ask because, the type of project someone might hire someone at your experience level to do is one that most likely wouldn't require you to make any users. You may be trying to do more work than is necessary.
In other words, don't bother making any users unless you know you need to. There is the default dbo user already there that will automatically get used for everything you need to do without you having to specify it anywhere.
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.
