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.

Syntax:

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	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_fixin 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.
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 loginmust 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 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 (+)	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 to Auto_Fix map an existing user to a login with the same name, and how to Mary create a B3r12-3x$098f6 SQL Server login with a password if no login exists 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 '    or     exec sp_ Change_users_login ' Auto_Fix ', ' username '/*3. If no login is established *    /exec sp_change_users_login ' auto_fix ', ' Login name ', NULL, ' login password '/ *--the same name can be used to 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