This is the third installment in a series on assigning SQL Server permissions. In the previous posts, we’ve looked at security principals (the users and groups that carry the permissions) as well as the securables (the objects you want to control access to). Building on this understanding, we’re getting ready to look at the fabric that connects principals with securables: Permissions.

Permissions

There are literally hundreds of different permissions in the SQL Server database engine, and you can see them all in the sys.fn_builtin_permissions() function.

SELECT *
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY class_desc, permission_name;

I won’t go into the details of trying to explain them all, but rather try to give you a general understanding of how they work and how they sometimes inherit each other. For reference, here’s a complete list of all permissions in the SQL Server database engine. But before we dive into that, we’ll take a more detailed look at the permissions of fixed database roles.

Permissions for fixed database roles

As we briefly touched on in the first post, each database comes with a set of fixed database roles. Each of those roles carries a predefined set of permissions (on the database level) with them.

db_owner

This is the “local administrator” of the database, although the database role doesn’t confer any server-level permissions to the login. Members of db_owner can do pretty much anything within the database, including creating/dropping objects, executing and viewing everything, adding/removing users and permissions – even dropping the database.

If you don’t want to give a user full db_owner membership, you can assign most of the permissions included in db_owner using the other remaining roles. The following permissions are specific for database and security administration and are only included in db_owner, but not in the other fixed database roles:

• TAKE OWNERSHIP (of the database)
• ALTER DATABASE
• ALTER ANY DATABASE AUDIT
• ALTER ANY DATABASE EVENT NOTIFICATION
• AUTHENTICATE (used for impersonation between databases)
• CONNECT REPLICATION
• CREATE DATABASE DDL EVENT NOTIFICATION
• EXECUTE
• IMPERSONATE
• RECEIVE (on object)
• SEND (on service)
• SHOWPLAN
• SUBSCRIBE QUERY NOTIFICATIONS
• VIEW DATABASE STATE
• VIEW CHANGE TRACKING

db_securityadmin

While db_securityadmin in itself doesn’t have any real access to data and schema, it is specifically designed to manage users, roles and permissions in the database. As such, members of db_securityadmin can assign any permissions or memberships within the database.

Warning: The ability to assign roles and permissions indirectly makes db_securityadmin equivalent to db_owner from a security perspective. Be very restrictive assigning this role to users.

db_accessadmin

Members of this role can add users and schemas in the database, although it cannot perform all the security tasks that db_securityadmin can. For instance, db_accessadmin cannot assign users to roles and cannot create application roles.

db_backupoperator

Members of this role can perform can perform backup-related tasks, BACKUP DATABASE, BACKUP LOG and CHECKPOINT.

Warning: Although membership in the db_backupoperator role does not give the user access to schemas or data, it is possible for a user to gain access to the information in a copy of the database by performing a backup and then restoring this backup on a different server.

db_datareader and db_denydatareader

The db_datareader role provides full SELECT permissions on all tables (and views that reference them). Conversely, the db_denydatareader role explicitly prevents SELECT on the same objects. This will effectively block users from reading any data in the database, even if they have been granted other, conflicting, permissions.

These permissions apply to user tables, not system tables.

db_datawriter and db_denydatawriter

db_datawriter allows users to make data modifications (the INSERT, UPDATE and DELETE statements) to any user table. In effect, UPDATE and DELETE will not work without SELECT permissions, because you cannot modify existing data without “seeing” what you are doing.

db_denydatawriter will explicitly prevent any changes to user data in the entire database, even when members may have conflicting permissions.

db_ddladmin

The db_ddladmin role gives members permissions to perform DDL operations like creating and altering tables, views, procedures, etc. It does not provide permissions to view data or assign permissions, nor does it automatically confer EXECUTE permissions on procedures or functions.

Permission hierarchy

Permissions are inherited in a tree-like structure (actually, a in a directed acyclic graph), where one permission can “cover” other implicit permissions with it. For instance, controlling a schema will grant you control over all objects that reside in the schema. SELECT permissions on a schema will give you SELECT permissions on all objects in that schema.

Every permission available in SQL Server can be viewed in the built-in function sys.fn_builtin_permissions. If you want to, you can create a “lookup” view using a recursive common table expression, where you can see how each permission implies other permissions:

WITH permissionTree (class_desc, permission_name,
         implicit_class_desc, implicit_permission_name, [level])
AS (
    --- Every type of permission there is:
    SELECT class_desc, permission_name,
           class_desc, permission_name, 0
    FROM sys.fn_builtin_permissions(DEFAULT)

    UNION ALL

    --- .. and a recursion to find all implicit permissions
    --- of those:
    SELECT tree.class_desc, tree.permission_name,
           fn.class_desc, fn.permission_name,
           tree.[level]+1
    FROM sys.fn_builtin_permissions(DEFAULT) AS fn
    INNER JOIN permissionTree AS tree ON
          tree.implicit_class_desc=fn.class_desc AND
          tree.implicit_permission_name=fn.covering_permission_name
        OR
          tree.implicit_class_desc=fn.parent_class_desc AND
          tree.implicit_permission_name=fn.parent_covering_permission_name)

SELECT class_desc, permission_name, [level],
       implicit_class_desc, implicit_permission_name
FROM permissionTree
ORDER BY 1, 2, 3, 4, 5;

There are also large posters for different versions of SQL Server, available for download on TechNet, where you can visually see the relations between permissions.

GRANT and DENY

Principals can be granted or denied permissions. GRANT is pretty straight-forward but it’s important to understand that DENY is not the same thing as no permissions at all. A DENY permission always has precedence over other GRANT permissions.

Example: A user has GRANT SELECT, INSERT, UPDATE, DELETE on a table. One of the roles that the user is a member of, however, has DENY DELETE on the same table. In this situation, the user can only SELECT, INSERT and UPDATE on the table.

REVOKE

Revoking a permission is not the same thing as DENY – rather, REVOKE is like “deleting” a permission, be it GRANT or DENY. The principal may still be granted or denied permission to a securable by means of another rule, for instance through group memberships, inherited permissions, etc.

WITH GRANT

You can grant permissions to a principal with delegation, known as WITH GRANT. This not only grants the principal permission to your securable, but it also allows the principal to GRANT the same permission to other principals.

Script to display all permissions

On the Downloads page, you’ll find a script that shows you all permissions on all objects in a database, both inherited and explicit. A great tool for security audits or just for securing your database properly. Check it out.

Summary

As usual, please let me know if there’s anything inaccurate or something that I may have left out. Check back next week for the fourth and final installment, where we’ll take a closer look at execution context, ownership chaining and impersonation!