And here I am writing a post about it [:)] Well I never would have imagined that but it was a long time ago and only clever people could put a website together back in those days when blogging platforms were a twinkle in some programmers eye.
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:
USE DatabaseName
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) :
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.
EXEC sp_change_users_login 'Auto_Fix', 'db_login1', NULL, 'aaZZww77';
Reference: http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users
