SQL Script to relink orphaned users to logins

It's quite a common task to have to move one or more databases from one server to another and this usually causes issues with orphaned users that have no related SQL login due to the SID values mismatching. The following is taken from an MS Knowledge base article about the problem:

When you move a database from one server that is running SQL Server to another server that is running SQL Server, a mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide the sp_change_users_login system stored procedure to map these mismatched users. However, you can only use the sp_change_users_login stored procedure to map standard SQL Server logins and you must perform these mapping for one user at a time.

Therefore the issue becomes how to sync up the logins in the copied database that relate to Login SID's that no longer exist on the server. If you try to manually re-save the login and grant it access to the copied database you are likely to run into problems with an error that prevents you from accomplishing the task such as:

User or role already exists in the current database

Therefore you either have to run a script to delete the users and then re-create them all correctly or do as I did and use some SQL to output the necessary TSQL statements that use the system views sys.sql_logins and sysusers and the stored procedure sp_change_users_login to relink the orphaned user to an SQL login.

-- example output to relink logins to users
USE myDB1
GO
EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase101', 'mydatabase101'
GO
USE myDB2
GO
EXEC sp_change_users_login 'UPDATE_ONE', 'mydatabase_admin_101', 'mydatabase_admin_101'
GO

The script has two options which allows you to either just output a list of all the orphaned users and their related databases so that you can run any reconfigure statements yourself. The other option will output all the necessary TSQL statements to relink the user to an SQL login of the same name.

IF @MODE = 'SQL'
BEGIN
 -- loop each orphan and generate auto fix code

 SELECT @MaxRow = COUNT(*), @RowID = 0
 FROM #RESULTS

 IF @MaxRow > 0
   BEGIN
  WHILE @RowID > @MaxRow
    BEGIN
   SELECT @RowID = @RowID + 1

   SELECT @DatabaseName = DatabaseName
    ,@OrphanedUser = OrphanedUser
   FROM #RESULTS
   WHERE RowID = @RowID

   -- output the TSQL to relink the user with the login

   SELECT @SQL =  'USE '   + @DatabaseName + + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)
      +'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + @OrphanedUser + ''', ''' + @OrphanedUser + '''' + CHAR(13) + CHAR(10)
      +'GO' + CHAR(13) + CHAR(10)

   PRINT @SQL
    END
   END

END
ELSE -- output the orphans
BEGIN
 SELECT *
 FROM #Results
 ORDER BY [DatabaseName], [OrphanedUser]
END

Obviously this is no good if you use different names for your logins and their corresponding users but as I don't it was perfect for me and enabled me to quickly resolve all the issues I had today when moving some large and important databases to a new server.

The code specifically ignores certain system users as well as all Windows Logins. It is specifically for SQL 2005 and above and is based on an SQL 2K script I found at the following site: vyaskn.tripod.com. If you require a script for an SQL 2K or SQL 7 server then I suggest checking out that site as this script will not work due to changes in the SQL System Views it uses.

To get the full source code please visit the following link:

Download the SQL

Tweet