Page Published [9-APR-2003]
Fix Orphaned Users in SQL Server after Restoring a Database to another Server
Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.
So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).
Common symptoms of this problem are:
- Applications may experience 'login failed' error messages and fail to log into the database.
- Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'
Solution:
Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.
--Change the user account to link with the 'NewHarry' login.
USE Books
go
EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'
This will change the user Harry in the restored database to link with NewHarry instead.
How to troubleshoot orphan users in SQL Server databases?
Posted @ 11:52 GMT