아래 스크립트를 사용하면 이 작업을 한번에 처리할 수 있다.
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/
'SQL Server > SQL Server Tip & Tech' 카테고리의 다른 글
모든 테이블의 데이터 일괄 삭제하기 (5) | 2009.03.04 |
---|---|
로그 DB 설계와 관리에 대한 컨셉 (슬라이딩 윈도우) (0) | 2009.02.25 |
SQL Server 2005 Performance Dashboard Reports에서 Difference of two datetime columns caused overflow at runtime 에러 Fix하기 (0) | 2008.09.22 |
64bit 환경에서 Excel 작업이 포함된 SSIS Package 실행하기 (1) | 2008.08.29 |
파티션 스키마를 참조하는 인덱스 목록 보기 (0) | 2008.08.14 |