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.

enter image description here

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.

• rsc: Isn't all just written in this error mesage? ;) Check user list of this database where you would like to create a new user. One login could be mapped to one user in given database.
• Rob: how can i do that. when creating database it automatically set the logged user as the owner of the database

Answer 1

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 2

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.

Answer 3

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.

Answer 4

1. Right-click on SQL Server instance at root of Object Explorer,
2. right click and select on Properties.
3. Select Security tab from the left pane.
4. Select the SQL Server and Windows Authentication mode radio button, and click OK.
5. restart the SQL Service Windows service. by (Start=> All Programs => Microsoft Sql Server 2012 =>Configuration Tool=> SQL Server Configuration Manager => SQL Server Services tab, Right click Sql Server=> Restart) hope help you

• Hannah Vernon: This won't prevent the error message the OP is experiencing.