How to create database, and add user linked to login?

Question

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.

Answer 1

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.