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'