Using sp_change_users_login to fix orphaned logins

by pezzar 14. April 2012 08:25

Picture the scenario, you've moved the enterprises mission critical database from one SQL Server instance to another. You bring the database online, all looks good. Alas, however, you have not created the server logins to provide the necessary database access. The boss wants to know why the application server cannot connect to the database, she's wanting heads to roll unless the application is back up and available in the next 2 minutes!!

 

Luckily, up to and including SQL Server 2012 you have a system stored procedure which handles the mapping of new and existing logins to database users, this procedure is deprecated in future versions of SQL Server but still useful in older versions, How does it work?

Firstly, the stored procedure sp_change_users_login is only used for resolving SQL Server orphaned logins, it takes a number of parameters which differ based upon the action to be performed. The syntax is as follows;

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

Valid actions are;

Auto_Fix - this will take the username and password parameters only. If the procedure finds an existing login with the same name the password will be ignored and the user account mapped. If the login does not exist it will be created with the specified parameters.

Report - this action takes no parameters, execute the procedure under your chosen database context and it will report any orphaned users within that database. The accounts are determined as orphaned if the unique SIDs do not match between the server level login and the database user, the name is not used here. You can check this by comparing the catalogs sys.server_principals in the Master database for the server login and sys.database_principals inside each user database for the database user. The report returns the ophaned user name and SID.

Update_One - this action is used to specifically map a database user back to an existing server level login, the login must exist.

  

Back to our scenario, you've contacted the application administrator and he's given you the account password from the web.config file, how do you proceed? We have the password and we know the login name which is the same as our database user, we issue the following query,

exec sp_change_users_login 'Auto_Fix', 'Jon', NULL, 'P@ssw0rd1'

 

The login is supplied as a NULL parameter as this will be created from the user name parameter, If the new login already existed the password parameter would have been ignored. Now, the more astute amongst you would have noticed that the server login and the database user don't have to have the same name, although possibly they should do for clarity. The default for the procedure using Auto_Fix will create a login of the same name, to map the user to a login of a different name, create the login  manually using CREATE LOGIN and then map the acccount. For example, to map the database user Jon to the new server login Bob use the following query,

 exec sp_change_users_login 'Update_One', 'Jon', 'Bob'

 

As an alternative to our scenario, the junior DBA in their panic, has manually created a new server login and is having trouble mapping the login to the database as the user already exists. Fortunately you catch him right before he drops the database user and avoid losing all granted permissions. You do this using the following query

exec sp_change_users_login 'Update_One', 'App_User', 'App_User'

 

Great, the application is back online, the boss declared you a hero and your peers are holding you high on a pedastool. And to think, you very nearly panicked and started dropping users from the database. Next time, you'll script the accounts from the source server and ensure they exist on the target server, hopefully! Wink