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.

created a login and a user in the master database and linked that user to the login.

I created a new database, then went to that database and immediately tried to create a user linked to the same login. I can't because the user dbo is already linked to the login.

In the master database, SELECT CURRENT_USER returns my WMC_SQLMGR user name which is linked to the WMC_SQLMGR login.

In the newly created WMC_main database, SELECT CURRENT_USER returns dbo as user name which is linked to the WMC_SQLMGR login.


CREATE login   WMC_SQLMGR WITH PASSWORD= 'pwstuff'
GO
(left out addsrvrolemember commands)

CREATE USER WMC_SQLMGR FOR LOGIN  WMC_SQLMGR
GO
CREATE DATABASE WMC_main ON PRIMARY
   ( NAME=N'WMC_main', ... (left out rest for brevity)
GO
ALTER LOGIN WMC_SQLMGR WITH DEFAULT_DATABASE = WMC_main  
GO
USE WMC_main
CREATE USER WMC_SQLMGR FOR LOGIN  WMC_SQLMGR  
   (this fails because the login    WMC_SQLMGR is connected to user dbo.)
***********************************************************
                DB_NAME    WMC_cur_user_name      sess_user_name user_name
---------------- ---------- -------------------- -------------- ----------
after USE MASTER master     WMC_SQLMGR           WMC_SQLMGR     WMC_SQLMGR

(1 row(s) affected)


-------------------------
database WMC_main dropped

(1 row(s) affected)


-------------------------
database WMC_main created

(1 row(s) affected)

                   DB_NAME    WMC_cur_user_name    sess_user_name user_name
------------------ ---------- -------------------- -------------- ----------
AFTER USE WMC_MAIN WMC_main   dbo                  dbo            dbo

(1 row(s) affected)


------------------------------------------------------
now try to create user WMC_SQLMGR for LOGIN WMC_SQLMGR

(1 row(s) affected)

Msg 15063, Level 16, State 1, Line 13
The login already has an account under a different user name.
share|improve this question
    
I don't understand what this has to do with dbo which is a fixed db principal. Can you show the code you're trying to use, and tell us the login name, the user name in master, and the user name you'd like to create in the new database? –  Aaron Bertrand Oct 5 '12 at 19:13
    
I ran this code on two different systems, and could not get it to fail. Can you please post the actual error message instead of your summary of the error message? Also does it work if you use FROM LOGIN (as in my example) instead of FOR LOGIN? Are you sure nothing else happened in between? –  Aaron Bertrand Oct 5 '12 at 19:57
    
I added more actual results to the original block. –  Dale Oct 8 '12 at 16:50
    
Can you try starting over with a completely different login name and user name? Can you also try starting over without the addsrvrolemember calls? I'm not sure how you are dropping the login and user in order to re-create it multiple times and reproduce the problem, but it may be that you are leaving something behind. As I said, I have tried your exact code and cannot reproduce the problem, so there must be something different that isn't obvious in the details you've provided. –  Aaron Bertrand Oct 8 '12 at 17:42
    
I added more actual results to the original block. All I want to do is create a login, create a database, set that the default database for that login to the new database, and create a user in that database linked to the login. –  Dale Oct 8 '12 at 19:24
show 1 more comment

1 Answer

Not sure how dbo is coming into play. Here is how you create a user in a database mapped to a server login, whether that login is already associated to a user in any other database:

CREATE LOGIN foo WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER foo FROM LOGIN [foo];
GO
-- then permissions:
GRANT ... TO foo;
EXEC sp_addrolemember N'<role>', N'foo';
etc.
share|improve this answer
    
@Dale can you please post that as part of your question? Code is very difficult to read in a comment. Also you created the user in master, great! Where is the code where you're trying to create the user in your new database? –  Aaron Bertrand Oct 5 '12 at 19:20
    
THe last statement to create the user WMC_SQLMGR under the WMC_main database fails because dbo is already connected to the login WMC_SQLMGR –  Dale Oct 5 '12 at 19:23
    
Also when I do use master select CURRENT_USER I get WMC_SQLMGR as the current user. when I do use WMC_main select CURRENT_USER I get dbo as the current user. –  Dale Oct 5 '12 at 19:26
    
Please delete your comments. Add the code samples and the actual error message to your question, where people will be able to read them. Also, are you executing any of this as WMC_SQLMGR? Why do you care what CURRENT_USER returns? –  Aaron Bertrand Oct 5 '12 at 19:32
    
I have done a little DBA work in the Oracle environment. I am new to the Windows/MS SQL environment and the permissions and user scope are a little confusing. I am trying to create my own logins and users so I understand all the privileges that are necessary without overlooking defaults. I was concerned about the CURRENT_USER being dbo in the new DB because I thought it might have an impact as I create new objects there. I am trying to create an environment for added applications that use an existing database that I do not want to modify or change (other than any required permissions) –  Dale Oct 5 '12 at 19:42
show 2 more comments

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.