아래 스크립트를 사용하면 이 작업을 한번에 처리할 수 있다.
1. User와 Login의 Sync가 맞지 않는 내역이 있는지 확인
DECLARE @nvcCollation sysname;
DECLARE @nvcStmt nvarchar(4000);
DECLARE @tblTemp table (
DBName sysname NOT NULL,
UserName sysname NOT NULL,
LoginName sysname NOT NULL
);
SET @nvcCollation = CAST(DatabasePropertyEx('master', 'Collation') AS sysname);
SET @nvcStmt = N'
USE [?];
SELECT ''?'', A.name, B.loginname
FROM sys.sysusers A
INNER JOIN master.dbo.syslogins B ON A.name COLLATE ' + @nvcCollation + N' = B.Name
INNER JOIN master.dbo.sysdatabases C ON C.name = ''?''
WHERE A.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 @tblTemp EXEC sp_msforeachdb @nvcStmt;
SELECT * FROM @tblTemp;
2. 위 스크립트에서 레코드가 반환된다면 => User와 Login을 Sync해 주기
DECLARE @nvcCollation sysname;
DECLARE @nvcStmt nvarchar(4000);
SET @nvcCollation = CAST(DatabasePropertyEx('master', 'Collation') AS sysname);
SET @nvcStmt = N'
USE [?];
DECLARE @nvcUserName sysname;
DECLARE @nvcLoginName sysname;
DECLARE SyncDBLogins CURSOR FOR
SELECT A.name AS userName, B.loginname AS loginName
FROM sys.sysusers A
INNER JOIN master.dbo.syslogins B ON A.name COLLATE ' + @nvcCollation + N' = B.Name
INNER JOIN master.dbo.sysdatabases C ON C.Name = ''?''
WHERE A.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 @nvcUserName, @nvcLoginName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @nvcUserName, @nvcLoginName;
FETCH NEXT FROM SyncDBLogins INTO @nvcUserName, @nvcLoginName;
END;
CLOSE SyncDBLogins;
DEALLOCATE SyncDBLogins;'
EXEC sp_msforeachdb @nvcStmt;
출처 : http://www.sqlservercentral.com/articles/Log+Shipping/63028/