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.
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?FROM LOGIN
(as in my example) instead ofFOR LOGIN
? Are you sure nothing else happened in between?