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:

1	USE DatabaseName

2	EXEC 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.

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) :

1	EXEC 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.

1	EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'aaZZww77';