SQL Server database user mapped to login User (Digest)

Recently a friend of SQL Server database accidentally shot, after several setbacks, the data restores the database to the new environment. After recovery, there is a user name at the database level and the instance level does not have a corresponding login user. This is a relatively common scenario in a SQL Server database. This article describes the mapping of these orphaned accounts through the system process sp_change_users_login.

1, the function of sp_change_users_login and its limitation
Use sp_change_users_login to link the database user in the current database to the SQL Server login name.
If the user logon name has changed, use sp_change_users_login to link the user to the new login without losing the user's permissions.
The new login cannot be SA, and user cannot be dbo, guest, or INFORMATION_SCHEMA.
Sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
Sp_change_users_login cannot be used with SQL Server logins created through Windows principals, or with users created by using create user without login.
Sp_change_users_login cannot be executed in a user-defined transaction.
Sp_change_users_login will be replaced by the ALTER user in subsequent versions.

2. sp_change_users_login Syntax Reference

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

3. Parameter description
[@Action =] ' Action '
Describes the action to be performed by the procedure. The data type of the action is varchar (10). Action can have one of the following values.

Value: Auto_Fix
Link the user in the sys.database_principals system catalog view of the current database to a SQL Server login with the same name. If a login with the same name does not exist, one will be created.
Check the results of the Auto_Fix statement to verify that the actual link is correct. Avoid using auto_fix in situations where security is more sensitive.
If the login does not already exist when using Auto_Fix, you must specify user and password, otherwise you must specify user, but password will be ignored. Login must be NULL.
User must be a valid user in the current database. You cannot map another user to the login.

Value: report
Lists the users in the current database that are not linked to any logins and the corresponding security identifiers (SIDs). User, login, and password must be NULL or unspecified.

Update_One
Links the specified user in the current database to an existing SQL Server login. User and login must be specified. The password must be NULL or not specified.

[@UserNamePattern =] ' User '
The user name in the current database. The data type of user is sysname, and the default value is NULL.

[@LoginName =] ' Login '
The name of the SQL Server login. The data type of login is sysname and the default value is NULL.

[@Password =] ' Password '
Assigns a password by specifying a new SQL Server login created by Auto_Fix. If a matching login already exists, the user name and login name are mapped and password is ignored.
If no matching logins exist, Sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login.
The password data type is sysname and cannot be NULL.

4. Examples of Use
A), find the current database orphaned users
EXEC sp_change_users_login ' report '

UserName UserSID
---------- -----------------------
Csidbo 0xafeef9da1ba20e43ac8b01c69574f91b

b), mapping orphaned users to logins with the same name (not present)
--The following example will create a new login named Csidbo, and set the password to XXX
--Demo environment: Microsoft SQL Server R2 (RTM)-10.50.1600.1
--Author:leshami
--Blog:http://www.linuxidc.com
Use crmnew;
GO
EXEC sp_change_users_login ' Auto_Fix ', ' csidbo ', NULL, ' xxx ';
GO

c), map the database user to an existing SQL Server login

Use crmnew;
GO
EXEC sp_change_users_login ' Update_One ', ' csidbo ', ' Csiuser '
GO

SQL Server database user mapped to login User (Digest)