Query to Find out Orphaned Users and Fixing them

When a restore of any database has been performed for example; SERVER A to SERVER B, Users cannot access the database on the SERVER B because they become Orphans even if they have Login ID on the SERVER B, to fix this. 

Run this to find out the list of Orphaned Users

EXEC sp_change_users_login 'Report'




After finding the orphaned users report, run the following command to Fix the Orphaned Users.

EXEC sp_change_users_login 'Auto_Fix', 'user'



To create a new login with password for user it can be achieved by running the below command. 

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'