Identify All Possible Permissions using sys.fn_builtin_permission

Introduction

SQL Server 2012 defines 214 different permissions that you can grant or deny. How do I know that? Well, there is an App catalog function for that: sys.fn_builtin_permissions

Listing Possible Permissions

If you are looking to find out what permission you can grant on a given securable, you could look in Books Online under the GRANT statement. That topic is actually quite substantial and you should take a look at it. However, sometimes BOL is not accessible or you just want a quick look at the possible values. In that case you can use the sys.fn_builtin_permissions catalog function. It returns one row for each possible permission, containing the grantable (or deniable) privilege and the securable class that the privilege applies to. To try the function out, let us run this query:

The parameter of the function can be used to restrict the output to one class of securables. By passing in for example 'database', only permissions that can be granted on a database will be returned. The output of above query looks like this:

The first two columns, permission_name and class_desc identify the privilege as well as the type of securable that this privilege can be granted on. The values in those columns match the values you would find in the sys.database_permissions and sys.server_permissions catalog views.

The covering_permission_name column identifies the next highest privilege on the same securable that implies the given privilege. If you look at the image above, you can see that for example the ALTER privilege on a database is implied by the CONTROL privilege on the same database. However, the CONTROL privilege itself is not implied by any other database level permission.

The parent_covering_permission_name and parent_class_desc columns identify a permission on the next level of the hierarchy of securables that implies the given permission. In above image you can see for example that the UPDATE privilege on an object is implied by the UPDATE privilege on the schema that the object belongs to. Also, while the CONTROL privilege, granted on a database, does not have an implying permission on the database level, it is implied by the server level CONTROL SERVER privilege.

The permission hierarchy is fully transitive. That means for example that the ALTER privilege for a database is also implied by the server level CONTROL SERVER privilege, because the database level ALTER privilege is implied by the database level CONTROL privilege which in turn is implied by CONROL SERVER as we had seen before.

Summary

The catalog function sys.fn_builtin_permissions can be used to list all possible permissions together with the securable classes they can be granted or denied on. The function can also be used to traverse the permission hierarchy to identify which permission is implied by which other permissions.