Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 17 '11 at 15:59

This question came from our site for professional and enthusiast programmers.

1  
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. –  rsc Nov 16 '11 at 7:59
    
how can i do that. when creating database it automatically set the logged user as the owner of the database –  Rob Nov 16 '11 at 8:13

4 Answers 4

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.

share|improve this answer
    
my only concern here is to create new schema for my database. do i get wrong about doing it so –  Rob Nov 16 '11 at 8:12
    
Are you doing this to learn or for a real project? I ask because, the type of project someone might higher 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. –  Brandon Moore Nov 16 '11 at 8:22
    
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. –  Brandon Moore Nov 16 '11 at 8:24

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.