DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them, but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

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'
Media Format: 
Topics: