Collection of methods for restoring orphaned users in a SQL Server database

Collection of methods for restoring orphaned users in a SQL Server database

Although SQL Server is now moving more and more, its own approach is becoming more advanced.

However, there are many orphaned users in our SQL Server relocation, Microsoft does not have automatic processing.

Because our database permissions tables are not in the application database, but each time the database is migrated, a single database with its database user objects.

And we can't log on to these accounts on the new database machine, but it's quietly in our database.

Microsoft previously provided an old interface stored procedure to handle this problem.

Sp_change_users_login

Maps an existing database user to a SQL Server login name. Subsequent versions of Microsoft SQL Server will remove the feature. Avoid using this feature in new development work, and start modifying applications that are currently using the feature. Please use ALTER USER instead.

Although it is said that the future version will not be supported, but several versions are supported.

Grammar:

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

Parameters:

[@Action =] 'action'

Describes the action to be performed by the procedure. The data type of the action is varchar (TEN). Action can have one of the following values.

Value	Description
auto_fix	The user item in the sys.database_principals  system catalog view of the current database is linked 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 you use   auto_fix   The login does not already exist, 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.
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	Assign the specified   in the current database; user   link to existing SQ L server  login . You must specify   user and   login . 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 sysnameand 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 passwordis 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 sysnameand cannot be NULL.

Return code value:

0 (Success) or 1 (failed)

Result set:

Column Name	Data Type	Description
UserName	sysname	The database user name.
UserSID	varbinary (85)	The security identifier of the user.

Comments:

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.

Permissions:

Membership of the db_owner fixed database role is required. Only members of the sysadmin fixed server role can specify the auto_fix option.

Example: A. Displays the current user's report for the login mapping

The following example generates a report of the users in the current database and their security identifiers (SIDs).

	Copy Code
EXEC sp_change_users_login ' report ';

B. Mapping a database user to a new SQL Server login

In the following example, the database user is associated with a new SQL Server logon name. The database user Mb-sales first maps to another login and then re-maps to the login name Maryb.

	Copy Code
--create the new login. CREATE LOGIN maryb with PASSWORD = ' 982734snfdhhkjj3 '; Go--map database user mb-sales to login maryb.use adventureworks2008r2; Goexec sp_change_users_login ' Update_One ', ' mb-sales ', ' maryb '; GO

C. Automatically map users to logins (create a new login if necessary)

The following example shows how to use Auto_Fix to map an existing user to a login with the same name, and how to create a SQL Server login with a password of b3r12-3x$098f6 without the presence of a logon name, Mary.

Actual combat:

    /* Many DBA friends may encounter one such problem,    when the data is restored to other servers, the login name will be lost, rebuilding is very troublesome,    especially the database users more cases, in fact, this problem solved very simple,    SQL Server 2005 and previous products,    SQL Server provides sp_change_users_login stored procedures (SQL Server 2008 requires alter USER)    Sp_change_users_ Login has three actions: Report,update_one and Auto_Fix    specific usage: *//*1. List orphaned user in current database */    exec  sp_change_users_login ' report '/*2. If you already have a login, map the user name to the specified login */    exec sp_change_users_login ' update_one ', ' username ', ' login '  ----most commonly used     or     exec sp_change_users_login ' auto_fix ', ' username '/*3. If there is no login user */    exec sp_change_users_login ' Auto_Fix ', ' Login name ', NULL, ' login password '/*--the same name can be used, handle orphaned users--execute on a single database */    exec sp_change_users_login ' auto_fix ', ' db_ Writer '    exec sp_change_users_login ' auto_fix ', ' db_reader '

Collection of methods for restoring orphaned users in a SQL Server database