Ravi Khanal

Database movement between servers is one of the common task for the DBA. But when the database is moved from one server to another or from one instance to other, the logins do not get transferred. So as a DBA you have to manually transfer the logins to remove this problem.

So let’s discuss what is the cause behind this. SQL Server logins are stored in the master database and they are mapped against individual databases. The Login informations on SQL Server 2005 are visible through sys.server_principals system catalog view and the sys.logins view. The mapping information of the database user is stored in the sysusers system table. This table includes the name of the database user and the SID of the corresponding SQL Server Login. The SID is the security identifier and SID tracks the logins throughout the entire systems.

So when we move or copy our database from one server to another or from one instance to another then the windows login will have the same SID but the SQL logins may have the different SIDs. So when the SID of the new server instance and the older instance doesn’t matches we have a Orphaned User.

So if you are copying or moving a database from one server to another, then you should follow these steps:

1. Backup the source database.
2. Create the logins on the destination server.
3. Restore the database on the destination server.
4. Synchronize logins if not completed in step 2.

So for Synchronizing the logins, first you need to check all the users that are present.
Select 'user', * from sys.sysusers

Then check whether those users have logins or not by
Select 'Login', * from sys.server_principals

If there is orphaned user for user ‘dave’ then you should create the login with the same SID as its SID in the source server. This can be done by

Create login dave with Password = 'password', SID=0x8103F8ADEEADFA44BC4FE23836BCCE93


Another way of synchronizing the login can be done by system stored procedure.

Exec sp_change_users_login Report

This code will list out all the orphaned users and their corresponding SID.

Then after this you can do

Exec sp_change_users_login AUTO_FIX, 'lisa', 'password'

So the Auto_Fix will create a new login with the new SID and then match those with the User’s SID.

After this also you have more orphaned user then you can do

Exec sp_change_users_login 'update one', 'john', 'password'

But there is one major problem with using the stored procedure sp_change_users_login, when you restore the database again then the sp_change_users_login will be gone. So you would have to again do the whole process again. So sp_change_users_login is fine if you are moving database only once. If it is a repeatable transfer of a database from server to server then this sp_change_users_login is not useful.

Login Synchronization can be done through SSIS as well. SSIS also uses random SIDs at the destination and sp_change_users_login type to work to remap the users accounts to the newly generated SIDs.

Another way of Synchronization can be done through sp_help_revlogin. So we need to first download sp_help_revlogin from the Microsoft’s kb246133, then execute sp_help_revlogin on the source and copy the result. Then run the copied result in the destination server. So this will solve the orphaned users problem.