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'

 

Tags:

SQL server

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count: