LearnNowOnline

Blog

User-Defined Server Roles in SQL Server 2012

Excerpt by Don Kiely | May 30, 2013

A long awaited security feature in SQL Server 2012 has been user-defined server roles. SQL Server has long had flexible user-defined database roles for database-level permissions (which you'll learn about later in this chapter), but with custom server roles you can finally get as granular with server-level permissions.

In old versions of SQL Server, the only way to grant some kinds of permissions to users was to assign them to a built-in fixed server role, which usually had way too many permissions. Making everyone a sysadmin was a horrible but common practice, particularly problematic because you can't deny a sysadmin anything. This violates the principal of least privilege in a big way, but was often a practical necessity. SQL Server 2005 and later made all this more granular, letting you assign just about any specific server-level permission to a user, but lacked the ability to group those permissions into a server role.

SQL Server 2012 solves that problem with its support for user-defined server roles. Creating a new server role is as simple as using the CREATE SERVER ROLE statement:

 CREATE SERVER ROLE LimitedDBA;

Then you can grant and deny any server-level permissions you want. The following code grants the CONTROL SERVER permission to the new role- akin to granting sysadmin privileges-then denies a few permissions to narrow down the privileges of the members of the server role. This is a very flexible way to grant the users who are members of the group specific permissions.

 -- Grant it virtual sysadmin permissions GRANT CONTROL SERVER TO LimitedDBA; -- And take some permissions away DENY ALTER ANY LOGIN TO LimitedDBA; DENY ALTER ANY SERVER AUDIT TO LimitedDBA; DENY ALTER ANY SERVER ROLE TO LimitedDBA; DENY CREATE SERVER ROLE TO LimitedDBA; -- Covered by ALTER ANY SERVER ROLE DENY UNSAFE ASSEMBLY TO LimitedDBA; 

To test the role, the code then creates a login associated with a Windows group, DBAs, on a machine named Willow, and adds the new login to the LimitedDBA role.

 CREATE LOGIN [WILLOW\DBAs] FROM WINDOWS; ALTER SERVER ROLE LimitedDBA ADD MEMBER [WILLOW\DBAs]; 

The code then creates a SQL Server login carol, with no permissions whatsoever within the instance of SQL Server. Then the code attempts various actions under carol's security context that require server-level permissions: create another login, view system information, and create another server role. All of these actions fail, as you can see in the Figure below, because the carol principal has no permissions to perform these actions.

 CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%'; EXECUTE AS LOGIN = 'carol'; CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA'; SELECT * FROM sys.dm_exec_cached_plans; CREATE SERVER ROLE CarolRole; REVERT; 

Next the code adds carol to the new LimitedDBA user-defined server role, and once again attempts to perform the same actions. As you can see in the Figure below, this time carol is able to get system information (the SELECT action), because that permission is granted through the CONTROL SERVER permission. But carol still can't create logins or server roles, because those permissions were explicitly denied from the LimitedDBA role.

 ALTER SERVER ROLE LimitedDBA ADD MEMBER carol; EXECUTE AS LOGIN = 'carol'; CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA'; SELECT * FROM sys.dm_exec_cached_plans; CREATE SERVER ROLE CarolRole; REVERT; 

In order to view all of the available server-level permissions that you can grant and deny to server roles, execute the following code. The Figure below shows the results.

 SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name; 

You can create user-defined server roles to grant users and groups a very specific set of permissions that they need to do their job, and no more. This is far more flexible than earlier versions of SQL Server, making security management far easier with SQL Server 2012, and easier management inevitably means a more secure server.

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.