Synchronizing SQL Logins

Update 2017-06-20

This does not work on Sql Azure. I believe the replacement is

alter user

Original Article

Do you ever move a database from one server to another?  If you do chances are your user ids will get out of sync since the access is granted to a sid instead of a username.  If after restoring your database you cannot login you can run the following command to see a list of orphaned user ids in your database.

sp_change_users_login ‘report’

Once you see the name of the orphaned user you can choose a user on the new server to connect it to. Run the following script to link the old user to the new user.

sp_change_users_login ‘update_one’,’OriginalUserFromFirstServer’,’DestinationUserOnNewServer’

I normally like these user names to match so the second and third arguments are almost always the same.