Category: SQL Server
When you restore a database backup to another server, you may experience a problem with orphaned users:
- 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:MSSQLBACKUPNorthwind.bak'
- Restore the database to a different SQL Server server:
RESTORE DATABASE Northwind FROM DISK = 'C:MSSQLBACKUPNorthwind.bak'
- The restored database contains a user named “test” without a corresponding login, which results in “test” being orphaned. Now, to detect orphaned users, run this code:
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:
Use Northwind go sp_change_users_login 'update_one', 'test', 'test'
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.
- 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:
Use master go sp_password NULL, 'ok', 'test'