How to fix SQL Server orphaned users

When you restore a database, often the UserSID is different between the database and the server users, so you end up with Orphaned Users.

To resolve this run the following when connected to the restored database.

EXEC sp_change_users_login ‘Report’

That will return a list of all orphaned users in the selected database.

If you already have a login id and password for this user, fix the orphaned user by:

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’

If you want to create a new login id and password for this user, fix it by :

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’