Tag Archives: SQL Server user-defined roles

User-Defined Server Roles in SQL Server 2012

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:

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.

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.

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.

SQL-Server-2012-Failed-Actions

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.

SQL-server-level-actions

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.

SQL-partial-list-of-server-level-permissions

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.

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

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.