Sync All Logins on a Server in a single click using SP_MSForEachDB

Ken, 2008-06-03

One of the problems restoring a database to a new server is dealing with out of sync logins. The link between a database user name and a server user name is the SID that is generated when the login is added to SQL Server. There are stored procedures to sync the SID's but they have to be run for each user that is out of sync in each database. If you are bringing up a DR server that houses 60 log shipped databases, this can be a very tedious task.

Preparing for a DR test, I decided to write a script that would look at all the users in a database and compare them to users in the master database and see if the SID's matched. If there was no match, I could then run the stored procedure to sync the login for that specific user. This was a tremendous help, but still had to be run against each database. Inevitably, a database was missed and a user reported that they could not login. It was an easy fix, but this is not the kind of attention you want during a DR test.

Then I remembered the sp_msForEachDB system stored procedure. It is a handy procedure that will execute a batch of statements for every database on the server. The database name can be referenced in the script by using a ? as a placeholder.

First run this script to see if there are any logins that are out of sync.

DECLARE @Collation varchar(100)
DECLARE @SQL VARCHAR(2000)
CREATE TABLE ##TempSync
(
DB_NME Varchar(50),
DBUserName varchar(50),
SysLoginName varchar(50)
)
SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))
SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
       A.name DBUserName,
       B.loginname SysLoginName
 FROM sysusers A
      JOIN master.dbo.syslogins B
      ON A.name Collate ' + @Collation + ' = B.Name 
      JOIN master.dbo.sysdatabases C
      ON C.Name = ''?''
 WHERE issqluser = 1
       AND (A.sid IS NOT NULL
       AND A.sid <> 0x0)
       AND suser_sname(A.sid) IS NULL
       AND (C.status & 32) =0 --loading
       AND (C.status & 64) =0 --pre recovery
       AND (C.status & 128) =0 --recovering
       AND (C.status & 256) =0 --not recovered
       AND (C.status & 512) =0 --offline
       AND (C.status & 1024) =0 --read only
 ORDER BY A.name'
INSERT into ##TempSync
EXEC sp_msforeachdb @SQL
SELECT * FROM ##TempSync
DROP TABLE ##TempSync

I have added some extra checks to only include databases that are online. There is no need to try and sync logins on a database that is in the middle of a restore. I am also getting the collation from the master database to make sure there are no conflicts with the other databases.

If the first script returns data, run this script to sync the logins.

DECLARE @Collation VARCHAR (100)
DECLARE @SQL VARCHAR(2000)

SELECT @Collation =CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)
DECLARE SyncDBLogins CURSOR FOR
 SELECT A.name DBUserName,
        B.loginname SysLoginName
 FROM sysusers A
      JOIN master.dbo.syslogins B
      ON A.name Collate ' + @Collation + ' = B.Name 
      JOIN master.dbo.sysdatabases C
      ON C.Name = ''?''
 WHERE issqluser = 1
       AND (A.sid IS NOT NULL
       AND A.sid <> 0x0)
       AND suser_sname(A.sid) IS NULL
       AND (C.status & 32) =0 --Loading
       AND (C.status & 64) =0 --pre recovery
       AND (C.status & 128) =0 --recovering
       AND (C.status & 256) =0 --not recovered
       AND (C.status & 512) =0 --offline
       AND (C.status & 1024) =0 --read only
 ORDER BY A.name

OPEN SyncDBLogins
FETCH NEXT FROM SyncDBLogins
 INTO @DBUserName, @SysLoginName

WHILE @@FETCH_STATUS = 0
 BEGIN
    EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName
    
    FETCH NEXT FROM SyncDBLogins
    INTO @DBUserName, @SysLoginName
 END
CLOSE SyncDBLogins
DEALLOCATE SyncDBLogins
'
EXEC sp_msforeachdb @SQL

It is always a good idea to run the first script again to make sure everything worked as planned. Also, this will only sync logins that already exist in the master database and the user database. If you are using a second server for DR, you should have a job that copies the logins from your primary server to your secondary server or you will find yourself doing a lot of typing and asking for passwords.

I guess one could argue that this is two or three steps instead of one like the title suggests, but technically only the second script needs to be executed. The rest of the steps are just there so you can confidently say "Yes. I'm sure the logins are in sync. I saw them in the result set before I ran the sync script and they were not in the result set after I ran it. It must be an application issue. The database is fine."