Thanks Ken
That script is very helpful. But I think that if you want to run the first script before and after running second why not have it as a combined script like the following.
DECLARE @Collation varchar(100)DECLARE @SQL VARCHAR(2000)
CREATE TABLE ##PreSync(DB_NME Varchar(50),DBUserName varchar(50),SysLoginName varchar(50))
CREATE TABLE ##AfterSync(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 ##PreSync
EXEC sp_msforeachdb @SQL
IF Exists(SELECT * FROM ##PreSync)
BEGIN
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
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 ##AfterSync
EXEC sp_msforeachdb @SQL
END
SELECT * FROM ##PreSync
SELECT * FROM ##AfterSync
DROP TABLE ##PreSync
DROP TABLE ##AfterSync