Resetting user names for orphaned logins

Sometime when we restore the database from a backup file in SQL Server, after that some logins are not allowed access to database. error like can’t login user ‘null’ in the database occur when connecting using code.

This happens because while restoring the database backup the login was not linked with the user name it was associated in the database whose backup we are using. To login into the DB using that login we need to assign that login a username.

@Office I need to restore databases many times and use them to debug to solve the issue. So I used to get this problem every time I restore the backup. Earlier to solve this issue, I used to use brute force method to resolve this like deleting the troubled login from the “Database\Security\Users” and deleting the schema related to this login. Then adding the same login again to “Database\Security\Users” . This used to solve my problem.

Microsoft SQL Server provides a procedure to solve this issue, which is in fact the right way to it. sp_change_users_login this sp is intended to relink the orphaned login with their user name. Following are the parameters to this procedure.

These parameters indicate what action to perform. It can be one of the 3 values

• Reports : Returns all the orphaned logins in the database
• Auto_Fix: Fixes the given login and sets the username to the user with the same name in database, if a user with same name is not found then a new entry in sys.database_principals table and the newly created entry is linked with the provided login.
• Update_One: updates the given login with the login name provided.

USE Demo_DB

EXEC SP_Change_User_Login 'Report'

The Report argument will return any orphaned users within the current database.

USE Demo_DB

EXEC SP_Change_User_Login 'Auto_Fix', 'SampleUser'

Above example displays usage of Auto_fix

USE Demo_DB

EXEC SP_Change_User_Login 'Update_One', 'SampleUser', 'SampleUser'

The above command will update the sampleuser user’s login to sampleUser.

Following are some references:

http://technet.microsoft.com/en-us/library/ms174378.aspx

http://blogs.msdn.com/blakhani/archive/2008/02/25/script-map-all-orphan-users-to-logins-else-create-login-and-map.aspx