Published on

December 12, 2007

Migrating Databases in SQL Server: Dealing with Orphaned Users

Migrating databases between different servers is a common task for database administrators. However, during the migration process, one of the issues that often arises is the concept of orphaned users. Orphaned users occur when the SQL login’s Security Identifier (SID) in the master..syslogins table does not match from server to server.

To better understand this issue, let’s consider a scenario where we have two SQL instances, SQL1 and SQL2. We will create a test database called Test_DB on both instances and create two logins, TestLogin1 and TestLogin2, with user rights to the Test_DB database.

After creating the database and logins, we can query the system tables (system views) in the master database to compare the SIDs of the logins on both instances. We can also query the sysusers system table (system view) in the Test_DB database to see the SIDs of the users.

At this point, the SIDs for the logins are different for each SQL instance. If we attempt to connect to the Test_DB database on either instance using the TestLogin1 or TestLogin2 login, we will be able to access the database without any issues.

Now, let’s fast forward a few weeks. We have been using SQL1 as our development server and SQL2 as our production server. We are preparing to migrate the Test_DB database from SQL1 to SQL2. After restoring the database on SQL2, we notice that the SIDs of the users in the Test_DB database do not match the SIDs of the logins in the master..syslogins table on SQL2.

As a result, when we attempt to connect to the Test_DB database on SQL2 using the TestLogin1 or TestLogin2 login, we encounter an error message stating that the server principal is not able to access the database under the current security context.

To resolve this issue, we can use the sp_change_users_login stored procedure provided by Microsoft. This stored procedure allows us to synchronize the SID values for SQL logins. We can use the “Update_One” action of the stored procedure to link a specified user in the current database to an existing login in the master database’s syslogins table.

By using a simple cursor, we can loop through all the SQL logins in the Test_DB database and reconcile their SID values with the corresponding logins in the master database. This process ensures that the users in the database are correctly linked to the logins at the instance level.

It is important to note that only SQL logins can become orphaned. Integrated security is recommended whenever possible to avoid this issue. Additionally, it is worth mentioning that relying on system tables should be done with caution, as they may be subject to changes in future versions of SQL Server.

In conclusion, migrating databases between SQL Server instances can be a complex task, especially when dealing with orphaned users. However, by understanding the concept of orphaned users and utilizing the sp_change_users_login stored procedure, database administrators can effectively manage and resolve this issue during the migration process.

As William Shakespeare once said, “Better three hours too soon than a minute too late.” This quote reminds us of the importance of addressing and resolving issues proactively, such as dealing with orphaned users during database migration.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.