Denali - Create Your Own SQL Server Server Roles

SQL Denali에서는 Server Role을 생성하여 계층적인 관리를 할 수 있게 되었습니다.

Server Role에서 마우스 오른쪽 버튼을 클릭해 주시면 간단히 Server Role을 생성할 수 있는 메뉴를 볼 수 있습니다.
 

예를 들어 우리의 서버에 생성된 Login 중 모니터링 부서에서는 사용하는 Login에게는 모니터링용 DMV를 호출하기 위해 “VIEW SERVER STATE”권한과 bulkadmin Server Role을 가져야 한다고 가정해 보겠습니다.
기존에는 새로운 Login이 생성될 때 마다 이런 권한을 매번 추가해 줄 수 밖에 없었습니다. 또는 기존의 권한에 setupadmin 권한을 추가해야 한다면 모든 모니터링용 Login에게 setupadmin을 하나하나 추가해 주어야 했었습니다.
하지만 SQL Denali의 이 새로운 기능을 이용하면 자신만의 Server Role을 생성한 다음 필요한 권한을 추가해 Login에 맵핑할 수 있습니다. 이제 새로운 Login이 생성된다거나 기존의 권한을 변경해야 할 경우 Login의 권한을 수정할 필요 없이 새로 생성한 Server Role에서 권한을 변경하면 됩니다. 

시나리오대로 Login 및 Server Role을 생성해 보겠습니다.

USE [master] GO   -- Login 생성 CREATE LOGIN [user1] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF CREATE LOGIN [user2] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF   -- Server Role 생성 CREATE SERVER ROLE myRole   -- Server Role에 필요한 권한 추가 ALTER SERVER ROLE bulkadmin ADD MEMBER myRole   -- ServerRole에 Login 맵핑 ALTER SERVER ROLE myRole ADD MEMBER user1 ALTER SERVER ROLE myRole ADD MEMBER user2 GO   이 상태에서 user1로 로그인 후 다음 쿼리를 수행해 보면 VIEW SERVER STATE권한이 없어 오류와 함께 쿼리 수행에 실패하게 됩니다. SELECT * FROM sys.dm_exec_query_stats   Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.   이제 다음과 같이 myRole에 VIEW SERVER STATE 권한을 추가 합니다. GRANT VIEW SERVER STATE TO myRole   다시 한번 user1로 로그인한 세션에서 sys.dm_exec_query_stats DMV를 조회해 보면 오류 없이 잘 수행 되는것을 확인할 수 있습니다.

이 기능을 통해 복잡한 권한 관리를 계층적으로 처리하여 권한 변경을 손쉽게 할 수 있게 되었습니다.

[참고]

Create Your Own SQL Server Server Roles

http://www.straightpathsql.com/archives/2010/11/create-your-own-sql-server-server-roles/

 

CREATE SERVER ROLE (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ee677610(v=sql.110).aspx

하만철 / Ha ManCheol