Problems with SQL Users after restoring SQL Server database
If you backup a SQL Server database accessed via SQL Server users (as opposed to Windows NT/domain users), then restore that database on another server, recreate the SQL Server users and attempt to apply permissions, you receive errors.
This occurs because the SQL user accounts are brought across from the original server but are not automatically linked to the SQL user accounts on the new server, even if they have the same name.
Remap the SQL user accounts as listed under the database with the user accounts as listed under Security / Logins:
- Open Query Analyser.
This shows the orphaned accounts.
- Then run:
sp_change_users_login 'update_one', 'user', 'user'
where user is the name of the orphaned user.