sp_change_users_login - Link users to corresponding logins

Introduction:
Although the terms login and user are often used interchangeably, they are very different. A login is used for user authentication and a database user account is used for database access and permissions validation. Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the SQL Server server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

Purpose:
The sp_change_users_login procedure has a specific purpose. It’s used to identify and correct users within a database which do not have a corresponding logins.

Scenario:
When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. A mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. An example of when this would happen is when you are restoring a database from Production to QA.

Syntax:

sp_change_users_login [ @Action = ] 'action' [ , [ @UserNamePattern = ] 'user' ] [ , [ @LoginName = ] 'login' ] [ , [ @Password = ] 'password' ]

Action: Describes the action to be performed.
Can be one of these values:

Value: Auto_Fix
Description: Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
Value: Report
Description: Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
user, login, and password must be NULL or not specified.
Value: Update_One
Description: Links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.

UserNamePattern:
It is the name of a SQL Server user in the current database.

LoginName:
It is the name of a SQL Server login.

Password:
It is the password assigned to a new SQL Server login created by Auto_Fix. If a matching login already exists, the user and login are mapped and password is ignored. If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login.

How to test the scenario?
1.
Add a login to the master database, and specify the default database as Northwind:
Use master go sp_addlogin 'test', 'password', 'Northwind'
2.
Grant access to the user you just created:
Use Northwind go sp_grantdbaccess 'test'
3.
Backup the database.
BACKUP DATABASE Northwind TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
4.
Restore the database to a different SQL Server server:
RESTORE DATABASE Northwind FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
5.
Now, to detect orphaned users, run this code:
Use Northwind go sp_change_users_login 'report'
The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the Northwind database, and the sysxlogins system table in the master database.

To re-link the user:
Use Northwind go sp_change_users_login 'update_one', 'test', 'test'