Moving SQL Server Databases - Orphaned Users
We've run in to an issue with logins when moving databases from an old SQL 2000 machine to a new SQL Server 2005 machine, using a simple backup and restore method. This works fine and restores the old SQL 2000 database to the new SQL 2005 machine.

However, any logins which existed in the old database will be scripted with the move and those logins won't be recreated in the global user logins. Therefore, in order to use the login we need to create a global login, too.

Problem! The global login can't be created because it already exists in the restored database and the restored login can't be deleted because it's attached to a database! This gives us an orphaned user on the restored database.

There are two solutions for this problem:
  1. Create a new global login, with a different name and attach it to the restored database.
  2. Relink the orphaned login using the system stored procedure sp_change_users_logi
The command to relink the orphaned user via a SP is as follows:
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'
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.

Only members of the sysadmin role can change the password for another user's login.

Published Friday, August 04, 2006