Thursday, 28 March 2013

Using sp_change_users_login ‘auto_fix’ to fix user/login

How to use sp_change_users_login to fix SQL Server orphaned users

Firstly, there may be a number of orphaned users, so the best thing to do is run this inside each database you are checking:
1USE DatabaseName
2EXEC sp_change_users_login 'Report';
You will see output like the screenshot attached if there are any orphaned users. In this example, user “db_login1″ is showing up as an orphaned user.
sp_change_users_login report
If you already have a login which you want to map your database user to, you could run the following (note that the first instance of ‘db_login1′ is the user in the database, the second instance is the login to be mapped to) :
1EXEC sp_change_users_login 'update_one''db_login1''db_login1';
If you don’t already have a login to map to, you can have sp_change_users_login create one for you and with a password. The following code does this and creates a login with the same name and a password of ‘aaZZww77′ as an example.
1EXEC sp_change_users_login 'Auto_Fix''db_login1'NULL'aaZZww77';
sp_change_users_login auto_fix