Using Server Roles for Server Level Security Management

Introduction

When managing permissions for multiple logins, server roles can simplify your life significantly. In most places where permissions need to be handled, some kind of grouping does exist, for example Windows Groups. Their purpose is to allow you to define a permission set for example based on a job requirement. After that set is defined once, you can grant that entire set to one person just by adding their login to that group. When that person does not have this specific job requirement anymore, you can just remove their login from the group.

This simplifies the administrators live significantly, particularly if a person is in multiple groups and is leaving only a few. If groups wouldn't exist, you would have to go through the list of all permissions and determine if each one is still necessary for any of that person's job requirements. With groups you just remove their login from the groups in question and the system handles the rest.

The New and Flexible User-Defined Server Role

Server roles in SQL Server have the same purpose. However, up to SQL Server 2008R2 their use was somewhat limited. SQL Server came with nine different server roles out of the box, all of which were immutable. That meant, we could not remove or add specific permissions to one of those roles. Creating new roles was also not possible.

SQL Server 2012 added fully functional user-defined server roles. You can create a new server role by simply executing this statement:

The role_name must be unique within the server. The authorization clause is optional and allows you to specify the owner of the new role. Once you have the role created you can add members with the ALTER SERVER ROLE Statement. This statement works the same for fixed and user-defined server roles. You can add any login (with the exception of 'SA') and any user-defined server role to any other server role.

Permissions

The permissions that can be granted to a server role are the same that can be granted to a login; they are all server level permissions. For permissions on objects that reside in a user database, a user or user role is required. SQL Server currently offers 30 different server level permissions. They include things like IMPERSONATE a login, CONTROL an endpoint or VIEW SERVER STATE. The complete list can be found here.

Summary

Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles that could not be changed. Starting with SQL Server 2012 we are now able to create our own server roles and grant (or deny) any combination of the 30 server level permissions. Any login and any other user-defined server role can be a member of such a new server role.