Dealing with orphaned users in SQL

SQL Server logins access individual databases using a database user that is mapped to the SQL Server login. There are two exceptions to this rule:

• The guest account. This is an account that, when enabled in the database, enables SQL Server logins that are not mapped to a database user to enter the database as the guest user.

• Microsoft Windows group memberships. A SQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

Information about the mapping of a SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Finding Orphaned Users

USE <database_name>;
GO
sp_change_users_login @Action='Report';

Outputs a list of users in the selected database that are not linked to any SQL Server login.

N.B.	sp_change_users_login cannot be used with SQL Server logins that are created from Windows.

Fixing orphaned users

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';

• See books online Troubleshooting Orphaned Users which is where the above information was copied from.