How to identify active server permissions using sys.server_permissions

Introduction

Yesterday I demonstrated how to identify active database permissions using the sys.database_permissions catalog view. That view returns a row for each permission that was defined in the current database. However, there are server wide permissions too, permissions that are not tied to a specific database. Those permissions are handled by a different catalog view: sys.server_permissions

Identifying active server permissions

Before we can see the sys.server_permissions catalog view in action, we need to first set up a few server wide permissions. For that, we need a suitable grantee:

That creates a login, and while we are at, a server role too. Now we can find a few permissions to grant:

Most server wide permissions do not have a securable mentioned in the GRANT statement, as the securable is the server itself. However, endpoints and availability groups are server wide securables too. To grant a permissions to either of those, you need to specify the securable type and name as in the ENDPOINT example above.

Now let us see what sys.server_permission has to report, by running the following statement:

Note, yesterday we used the USER_ID() function to filter the result to include only the two database principals we were interested in. Today, as we are looking for server principals, we have to use the SUSER_ID() function instead.

The above query will return this result:

There are three permissions returned, however, we did set up only two. The third one is the default permission that allows a login to connect to the server. It is granted automatically when the login is created.

The column list is actually the same that the sys.database_permission catalog view returns. Let us to a quick recap:

• class_desc: This identifies the type of securable for the permission. Most server permissions will return the value SERVER in this column.
• major_id and minor_id: These two values identify the securable itself. For most server permissions both will be 0. However, for example for endpoints, major_id matches the endpoint_id of the enpoint. I believe, minor_id is always 0 for server permissions.
• grantee_principal_id: This column identifies the server principal that the permission was granted to. You can use it for example to join to sys.server_principals.
• grantor_principal_id: This identifies the server principal that granted the permission. This helps SQL Server for example with REVOKE CASCADE . You can use this one too to join to sys.server_principals.
• permission_name: This value identifies the privilege itself. You will find values like CONNECT SQL, ADMINISTER BULK OPERATIONS or CONTROL SERVER in here.
• state_desc: Here you can see if the permission was granted or denied. WITH GRANT OPTION is also identified here. The documentation mentions the value REVOKE to be possible too. However, I am not aware of any actual case in which you would see that value.

As in the case of sys.database_permissions, 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

sys.server_permissions can be used to identify or list active server permissions. It returns one row for each granted or denied permission with server scope.