Server-Level Roles – Back to Basics

Server-Level Roles

SQL Server security is like a box of chocolates. Wait, it is more like an onion – with all of the layers that get to be peeled back. One of the more important layers, in my opinion, is the layer dealing with Roles.

I have written about the various types of roles on several occasions. Whether it be Fixed Server Role memberships, Fixed Server Role permissions, or Database Roles permissions (among several options), you can presume that I deem the topic to be of importance.

Within the “Roles” layer of the SQL Server security onion, there are multiple additional layers (as alluded to just a moment ago) such as Database Roles and Server Roles. Focusing on Server Roles, did you know there are different types of Server Roles? These types are “fixed roles” and “custom roles.” Today, I want to focus on the custom type of role.

Custom Server Roles

Starting with SQL Server 2014, we were given a new “feature” to help us minimize our security administration efforts. The new “feature” is that which allows a data professional to create a “Server Role” in SQL Server and to grant specific permissions to that role. I wrote about how to take advantage of this in the 2014 recipes book I helped to author, but never got around to creating an article here on how to do it.

In this article, I will take you through a quick example of how to take advantage of these custom roles.

First let’s create a login principal. This principal is a “login” so will be created at the server level. Notice that I perform an existence check for the principal before trying to create it. We wouldn’t want to run into an ugly error, right? Also, when you use this script in your environment, be sure to change the DEFAULT_DATABASE to one that exists in your environment. While [] is an actual database in my environment, it is highly unlikely it exists in yours!

USE [master];
GO
IF NOT EXISTS
(
    SELECT name
    FROM sys.server_principals
    WHERE name = 'Gargouille'
)
BEGIN
    CREATE LOGIN [Gargouille]
    WITH PASSWORD = N'SuperDuperLongComplexandHardtoRememberPasswordlikePassw0rd1!'
       , DEFAULT_DATABASE = []
       , CHECK_EXPIRATION = OFF
       , CHECK_POLICY = OFF;
END;

Next, we want to go ahead and create a custom server level role. Once created, we will grant specific permissions to that role.

--check for the server role
IF NOT EXISTS
(
    SELECT name
    FROM sys.server_principals
    WHERE name = 'SpyRead'
          AND type_desc = 'SERVER_ROLE'
)
BEGIN
    CREATE SERVER ROLE [SpyRead] AUTHORIZATION [securityadmin];
    GRANT CONNECT ANY DATABASE TO [SpyRead];
    GRANT SELECT ALL USER SECURABLES TO [SpyRead];
END;

As you can see, there is nothing terrifyingly complex about this so far. The statements should be pretty familiar to the data professional and they are fairly similar to routine tasks performed every day. Note in this second script that after I check for the existence of the role, I simply use “CREATE SERVER ROLE” to create the role, then I add permissions explicitly to that role.

Now, I will add the login “Gargouille” to the Server Role “SpyRead”. In addition to adding the login principal to the role principal, I will validate permissions before and after – permissions for Gargouille that is.

EXECUTE AS LOGIN = 'Gargouille'
GO
USE [];
GO
SELECT *
FROM fn_my_permissions(NULL, 'DATABASE') fn;
REVERT
USE master;
GO
IF NOT EXISTS
(
    SELECT mem.name AS MemberName
    FROM sys.server_role_members rm
        INNER JOIN sys.server_principals sp
            ON rm.role_principal_id = sp.principal_id
        LEFT OUTER JOIN sys.server_principals mem
            ON rm.member_principal_id = mem.principal_id
    WHERE sp.name = 'SpyRead'
          AND sp.type_desc = 'SERVER_ROLE'
          AND mem.name = 'Gargouille'
)
BEGIN
    ALTER SERVER ROLE [SpyRead] ADD MEMBER [Gargouille];
END;
EXECUTE AS LOGIN = 'Gargouille'
GO
USE [];
GO
SELECT *
FROM fn_my_permissions(NULL, 'DATABASE') fn;
REVERT

We have a few more things happening in  this code snippet. Let’s take a closer look and break it down a little bit. The first section tries to execute some statements as “Gargouille”. When this attempt is made, there is an error that is produced – which is good because it validates the principal does not have permission to connect to the requested database.

The next statement of  interest adds the “Gargouille” principal to the SpyRead Server role. After the principal is added to the custom server role, I attempt to impersonate the “Gargouille” principal again and connect to the database and run a permissions check. These are the results from that last query.

Lastly, I run a check to validate that Gargouille is indeed a member of the server role “SpyRead” – which it is. From these results we can see the power of the customer server role. In this case, I had a user that “needed” to access every database on the server. Instead of granting permissions on each database one by one, I granted the “Connect” (and a couple of other permissions to be discussed in the follow-up article) to the server role and then added Gargouille to that role. This reduced my administration time requirement quite a bit – more if there are hundreds of databases on the server.

In the follow-up article, I will show how this will help to make it easier to grant a user the ability to view schema definitions as well as read from every database with one fell swoop. Stay tuned!

Wrapping it Up

In this article, I have shown how to use the power of custom server roles to help reduce your administration time. The custom security role is like using a security group to grant a bunch of people the same sets of permissions. When you use a security group to manage multiple people, it makes administration very much like you have offloaded the job to somebody else to do because it becomes that easy!

Now it is your turn, take what you have learned in this article and see how you could apply it within your environment to help you be a rockstar data professional.

Feel free to explore some of the other Back to Basics posts I have written.

Are you interested in more articles showing what and how to audit? I recommend reading through some of my auditing articles. Feeling like you need to explore more about the security within SQL Server, check out this library of articles here.