This site is hosted and sponsored by hyve.com specialists in Cloud Hosting UK and VMware Hosting. If you are interested in our services please call us for chat on 0800 612 2524

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:\MSSQL\BACKUP\Northwind.bak'

• Restore the database to a different SQL Server server:

RESTORE DATABASE Northwind
FROM DISK = 'C:\MSSQL\BACKUP\Northwind.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'

Back to SQL Server