Using Database Roles for Database Level Security Management

Introduction

Over the last two days I introduced you to server roles and their member management. Server roles can simplify the management of server level permissions. However, for database level permissions they are not the right tool.

To manage database level permissions SQL Server offers a similar solution: Database Roles. Any user that is a member of a database role inherits the permissions granted to that role.
The only real difference between server roles and database roles is that database roles get defined in a particular database whereas server roles are defined for the entire server. Any member of a database role must be a principal defined in the same database that the role is defined in.

Create a Database Role

The statement to create a database role is very similar to the one to create a server role:

Notice that the word DATABASE is not part of this statement. Historically (before SQL 2012), SQL Server had only nine fixed server roles, so any user-defined role was a database role. So the word database was implied and not called out. That is reflected in the syntax of that create statement.

The statement has to be executed while inside the database that you want to create the role in and role_name has to be unique within that database. The AUTHORIZATION clause is optional and specifies the owner of the new role.

After the role is created, permissions can be set with GRANT, DENY or REVOKE and members can be added with the ALTER ROLE statement.

Permissions

Permissions that can be granted to a role are all local to the database including for example CONNECT to the database, CREATE SCHEMA or SELECT from a table. Any permission granted to the role will be effective for all its members.

Fixed Database Roles

SQL Server comes with nine fixed database roles, the most prominent being db_owner. Similar to the fixed server roles, the fixed database roles cannot be changed. Any attempt to grant or deny permissions to a fixed database role will result in an error.

Summary

User-defined database roles provide the same maintenance simplification on a database level that server roles offer on the server level. SQL Server comes with nine fixed database roles out of the box that address common job requirements. Permissions granted to a database role get inherited by every member of that role.