Home > Database Administration, Security, SQL Server, SQL Server 2012 > User-defined Server Roles in SQL Server 2012

User-defined Server Roles in SQL Server 2012

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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: