Ing SQL Server database users to login users

Ing SQL Server database users to login users

Recently, a friend of mine accidentally shot the SQL server database. After several twists and turns, the data restored the database to the new environment. After recovery, there will be a user name at the database level, while there will be no corresponding login user at the instance level. This is a common situation in SQL server databases. This article describes how to establish a ing between these isolated accounts through the system process sp_change_users_login.

1. sp_change_users_login functions and restrictions
Use sp_change_users_login to link the database users in the current database to the SQL Server login name.
If the user's logon name has been changed, use sp_change_users_login to link the user to a new logon without losing the user's permissions.
The new login cannot be sa, but the user cannot be dbo, guest, or INFORMATION_SCHEMA.
Sp_change_users_login cannot be used to map database users to Windows-level subjects, certificates, or asymmetric keys.
Sp_change_users_login cannot be used together with the SQL Server LOGIN name created through the Windows subject or with the USER created using the CREATE USER WITHOUT LOGIN.
You cannot execute sp_change_users_login in a user-defined transaction.
Sp_change_users_login will be replaced by alter user in later 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 operations to be performed. The data type of action is varchar (10 ). Action can have one of the following values.

Value: Auto_Fix
Connect the user necklace in the sys. database_principals system directory view of the current database to the SQL Server login name with the same name. If there is no login name with the same name, one will be created.
Check the result of the Auto_Fix statement and check whether the actual link is correct. Avoid using Auto_Fix when you are sensitive to security.
If the username does not exist when Auto_Fix is used, the user and password must be specified. Otherwise, the user must be specified, but the password will be ignored. Login must be NULL.
The user must be a valid user in the current database. You cannot map another user to this login name.

Value: Report
List users that are not linked to any login name in the current database and their corresponding Security Identifiers (SID ). User, login, and password must be NULL or not specified.

Update_One
Link the specified user in the current database to the existing SQL Server login. User and login must be specified. Password Must be NULL or not specified.
 
[@ UserNamePattern =] 'user'
The username in the current database. The user data type is sysname, and the default value is NULL.

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

[@ Password =] 'Password'
The password assigned by the new SQL Server login name created by specifying Auto_Fix. If a matched login name exists, the user name is mapped to the login name and the password is ignored.
If no matched login name exists, sp_change_users_login creates a new SQL Server login name and assigns a password as the password for the new login name.
The password data type is sysname and cannot be NULL.

4. Example
A) Find isolated users in the current database
Exec sp_change_users_login 'report'

UserName UserSID
---------------------------------
Csidbo 0xAFEEF9DA1BA20E43AC8B01C69574F91B

B) map isolated users to login names with the same name (nonexistent)
-- In the following example, A New Login Name csidbo is created and the password is set to xxx.
-- Demo environment: Microsoft SQL Server 2008 R2 (RTM)-10.50.1600.1
-- Author: Leshami
-- Blog: