How to identify active database permissions using sys.database_permissions

Introduction

I have written quite a few posts about granting, revoking and denying permissions over the last month. Today I would like to show you a catalog view that you can use to see all the permissions that have been granted in the current database.

Identifying active database permissions

As usual, we first need to create a few things to be able to see this catalog view in action:

With that taken care of, we can set up two granted and a denied permission:

To see the above permission, we are going to use the sys.database_permissions catalog view like this:

The sys.database_permissions catalog view returns one row for each permission that has been granted or denied in the current database. In our case, the above query will return the following result:

Let us look at the columns individually:

• class_desc: This identifies the type of securable that this permission pertains to. You need this value to be able to correctly interpret major_id and minor_id.
• major_id and minor_id: These two values identify the securable itself. You can use them to join to other catalog views like sys.objects, sys.columns or sys.certificates. Which catalog view to join to can be determined using the value in the previous column. minor_id is only used for column permissions and represents the column_id of the column the permission was defined on. In all other cases it returns the value 0.
• grantee_principal_id: This column identifies the database principal that the permission was granted to. You can use it for example to join to sys.database_principals.
• grantor_principal_id: This identifies the principal that granted the permission. It is for example used by SQL Server for the REVOKE CASCADE logic. As with the previous column, you can join to sys.database_principals to get more information about the principal.
• permission_name: This value identifies the privilege itself. You will find values like SELECT, EXECUTE or CONTROL in here.
• state_desc: Here you can see if the permission was granted or denied. WITH GRANT OPTION is also identified here. You might even find the value REVOKE in here. I will show you an example of the latter in a later post.

The columns that are not part of the above list each contain some SQL Server internal representation of one of the values described above.

Summary

The sys.database_permissions catalog view can be used to identify active database permissions that are defined in the current database. It returns one row for each granted or denied permission.