Detect and Correct Orphaned Users
To detect orphaned users in a given database, simply run the following.
USE [MyDB] GO EXEC sp_change_users_login 'report'
If you want to check all databases on a given SQL Server at the same time, just wrap it in sp_msforeachdb.
EXEC sp_msforeachdb "exec [?]..sp_change_users_login 'report'"
Finally to resolve an orphaned user, run the following script to resync the SID of the user to map to the appropriate login. Note that a user does not need to map to a login of the same name just as the login 'sa' maps to the user 'dbo'.
USE [MyDB] GO EXEC sp_change_users_login 'update_one', 'YourUser', 'YourLogin'