This article describes how you can troubleshoot orphaned users.
When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."
Troubleshooting Orphaned UsersWhen you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.
- Add a login to the master database, and specify the default database as Northwind:
Use master go sp_addlogin 'test', 'password', 'Northwind'
- Grant access to the user you just created:
Use Northwind go sp_grantdbaccess 'test'
- Backup the database.
BACKUP DATABASE Northwind
TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
- Restore the database to a different SQL Server server:The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
RESTORE DATABASE Northwind
FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
- Now, to detect orphaned users, run this code:The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the Northwind database, and the sysxlogins system table in the master database.
Use Northwind go sp_change_users_login 'report'
Steps To Resolve Orphaned Users
- Run the following command for the orphaned user from the preceding step:This relinks the server login "test" with the the Northwind database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.
sp_change_users_login 'update_one', 'test', 'test'
- After you run the code in the preceding step, the user can access the database. The user may then alter the password with the sp_password stored procedure:This stored procedure cannot be used for Microsoft Windows NT security accounts. Users connecting to a SQL Server server through their Windows NT network account are authenticated by Windows NT; therefore, their passwords can only be changed in Windows NT.
sp_password NULL, 'ok', 'test'
Only members of the sysadmin role can change the password for another user's login.
Article ID: 274188 - Last Review: Jul 10, 2008 - Revision: 1