Recently after restoring a database backup which i got from another server i repeatedly encountered an error when logged in using a user who is given permission. But when ‘sa’ account is used i could work with the database without any problem.
Later i found that the issue is because the user in the restored database is not properly mapped to the user in the new server and the resolution for this is to run the stored procedure sp_change_users_login to correct the orphaned user.
- sp_change_users_login ‘update_one’, ‘USER’, ‘LOGIN’ — Links the given user in the current database to the specified login.
sp_change_users_login ‘suto_fix’, ‘USER’ –Links the given user in the current database to the login having the same name in the current server.
After running this you will be able to access the restored database without any issue using the mentioned database user.
System Admin Tech Tips 