Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

By Ken Simmons,

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."

 

Total article views: 13408 | Views in the last 30 days: 21
 
Related Articles
SCRIPT

Change Database Collation

A stored procedure to automate database collation change

FORUM

Script to Change Collation of User Database

without affecting master databases & other user databases collation

FORUM

Which Column Collation Do I Need To Use????

Multilingual database collation problems

FORUM

Collation issue between tempdb and the database xxx - Better Solution?

Collation issue between tempdb and the database xxx - Better Solution?

FORUM

Collation

Incorrect Collation

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones