Orphaned users

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: