Resetting security after restoring a database backup

When you restore a database from one server to another (i.e. restoring from Production to QA or Development), the user permissions of SQL logins are not automatically reset even if a user with the same name exists (note that this does not apply if you use Windows authentication)

In order to reset the user permissions, you can use the system command sp_change_users_login.

When passing REPORT as a parameter, it will list all the orphan users.

sp_change_users_login REPORT
GO

You can then re-assign the orphan users to existing users by using the following command:

sp_change_users_login UPDATE_ONE, 'ORPHAN_USER', 'LOGIN'

Example:

sp_change_users_login UPDATE_ONE, 'production_account', 'qa_account'
GO