How to Associate SQL Server Login with Existing Database User

Description

Sometimes a SQL Server Login  is created and corresponding Database User is created at the same time.  However, sometimes the Database User is not associated with a SQL Server Login and needs to be.  For example, this could happen during an database environment migration or if when the Active Directory User Account associated with SQL Server Login is deleted and a new one is created.


Solution

The steps below can be used to lookup database principals and then associate a Login with a User.

1.  Run the SELECT query below to view all database principals, including:
- DATABASE_ROLE
- SQL_USER
- WINDOWS_USER

SELECT * FROM sys.database_principals


2.  Run  ALTER USER query below to alter the database user and associate a login with it.  In the example below, a Windows Login is being associated with a database user.

ALTER USER "DOMAIN\Username"
WITH
LOGIN = "DOMAIN\Username"

(Microsoft TechNet, 2014)


References

Bertrand, A. (November, 2013).  Script to Set the SQL Server Database Default Schema For All Users.  Retrieved January 7, 2014 from
http://www.mssqltips.com/sqlservertip/3098/script-to-set-the-sql-server-database-default-schema-for-all-users/

Microsoft TechNet (2014).  ALTER USER (Transact-SQL).  Retrieved January 7, 2014 from http://technet.microsoft.com/en-us/library/ms176060.aspx

Tharaka MTR (May, 2013).  How to Fix Orphaned SQL Users.  Retrieved January 7, 2014 from http://www.codeproject.com/Articles/594134/How-to-Fix-Orphaned-SQL-Users