Archive

Archive for the ‘Security’ Category

User-defined Server Roles in SQL Server 2012

07/11/2011 Leave a comment

In SQL Server 2008 R2 we can create database role, assign appropriate permission to it and then add users or groups as a member of database role. It works well for database level permissions. For server level permissions, we can only add user in one or more predefined fixed server roles or assign individual permissions to it. Usually, my users don’t need all permissions from the fixed server role and I like to add least privilege to my users, especially on server level,  so I end up by giving individual server level permissions to individual users. That will change in SQL Server 2012.
User-defined server role is one of the new security enhancements in SQL Server 2012. It can be created and managed using the CREATE SERVER ROLE, ALTER SERVER ROLE and DROP SERVER ROLE commands, or we can use SSMS UI.  Only server scope permission can be added to server roles. To see all built-in permissions that can be assigned to server role we can run:

SELECT * FROM sys.fn_builtin_permissions(default)
WHERE class_desc in ('SERVER', 'ENDPOINT','LOGIN','AVAILABILITY GROUP','SERVER ROLE')
ORDER BY class_desc

I decided to test new feature by creating server role for a performance checking. It should allow the user to do some server checking, running DMVs, reading definitions, running server side traces and extended event sessions. Next script will create server role PerfRole with required permissions and will add PerfLogin as a member of the role.

USE [master]
GO
CREATE SERVER ROLE [PerfRole] AUTHORIZATION [sa]
GO
GRANT CONNECT SQL TO [PerfRole]
GRANT VIEW ANY DATABASE TO [PerfRole]
GRANT VIEW ANY DEFINITION TO [PerfRole]
GRANT VIEW SERVER STATE TO [PerfRole]
GRANT ALTER ANY EVENT SESSION TO [PerfRole]
GRANT ALTER TRACE TO [PerfRole]
GO

ALTER SERVER ROLE [PerfRole] ADD MEMBER [PerfLogin]
GO

I log in as a PerfLogin and everything work fine. I can run DMVs, server side traces and SQL profiler. I can create and manage Extended Event session. I can see what was happening on the server without having to be in the sysadmin role or have individual permissions assigned to my login.

As you know, member of fixed server role can add members to the same role, but member of a user server role will not have permission to add members to the same role.  Login must have, at least, ALTER permission on that role to add members to the role.

GRANT ALTER ON SERVER ROLE::[PerfRole] TO [PerfLogin]
GO

If you want that all members of a user defined role can add members to the role you can give ALTER permission to the role.

GRANT ALTER ON SERVER ROLE::[PerfRole] TO [PerfRole]
GO

User-defined server roles are nice addition to SQL Server 2012. These will simplify administration of instance-level rights and help increase the security of SQL Server instances.