재해복구를 위한 SQL Server 역할 가져오기

재해복구를 위한 SQL Server 역할 가져오기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server를 다른 서버로 이전하거나 DR에 의해서 다른 사이트로 이동 될 때 반드시 챙겨야 하는 부분이 로그인 계정과 서버 역할이다. 서버 역할을 복구할 수 있는 스크립트를 만들어서 언제든지 서버 역할을 복원 할 수 있는 방법에 대해서 알아 본다.

 

서버 역할에 대한 정보를 확인 하기 위해서는 다음 두 개의 보안카탈로그 뷰를 사용할 수 있다.

• sys.server_principals : 로그인의 이름, 시스템 로그인 및 고정 서버 역할을 확인
• sys.server_role_members : 서버 역할 멤버 자격 및 고정 서버 역할 멤버의 principal_id 확인

 

다음 스크립트는 서버 역할을 확인하고 해당 서버 역할이 존재하지 않는 경우 서버 역할을 생성한다. Principal_id로 필터링 하는 이유는 시스템 관리자 및 securityadmin 같은 고정 서버 역할은 생성 할 필요가 없기 때문이다. 임시로 test_role 역할을 생성하고 스크립트를 생성해보면 사용자가 추가한 서버 역할을 생성하는 스크립트가 생성됨을 확인 할 수 있다.

SET NOCOUNT ON;   SELECT 'IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE type = ''R'' AND name=''' + [name] + ''') CREATE SERVER ROLE [' + [name] + '];' FROM sys.server_principals WHERE type = 'R' AND principal_id > 10;

 

 

 

다음 스크립트는 역할 구성원을 복원한다. 역할에 추가하려는 계정이 존재하지 않을 경우 오류가 발생 한다. 생성된 스크립트를 실행하면 역할에 추가 된다.

SET NOCOUNT ON;   SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + p.[name] + ''', @rolename = ''' + r.[name] + ''';' FROM sys.server_principals AS p JOIN sys.server_role_members AS srm ON p.principal_id = srm.member_principal_id JOIN sys.server_principals AS r ON srm.role_principal_id = r.principal_id WHERE p.[name] <> 'sa';

 

 

 

[참고자료]

• Retrieving SQL Server Server Roles for Disaster Recovery :

http://www.mssqltips.com/sqlservertip/2288/retrieving-sql-server-server-roles-for-disaster-recovery/

• sys.server_principals : https://msdn.microsoft.com/ko-kr/library/ms188786.aspx
• sys.server_role_members : https://msdn.microsoft.com/ko-kr/library/ms190331.aspx
• sp_addsrvrolemember : https://msdn.microsoft.com/ko-kr/library/ms186320.aspx

 

 

2015-04-07 / 강성욱 / http://sqlmvp.kr

 

SQL Server, mssqlserver, 서버 역할, 로그인, DB보안, 사용자 보안, server role, sp_addsrvrolemember, sys.server_principals, SQL 보안, 사용자 역할