How to identify active server permissions using sys.server_permissions

2014-02-23 - DMVs & CVs, General, Security, Security

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:

CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE SERVER ROLE TestServerRole1;
ALTER SERVER ROLE TestServerRole1 ADD MEMBER TestLogin1;

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

DENY CONNECT ON ENDPOINT::[TSQL Default TCP] TO TestLogin1;
GRANT SHUTDOWN TO TestServerRole1;

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:

SELECT * FROM sys.server_permissions AS SP
WHERE SP.grantee_principal_id IN (SUSER_ID('TestLogin1'), SUSER_ID('TestServerRole1'));

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:

Identifying active server permissions.

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.

Categories: DMVs & CVs, General, Security, Security
Tags: , , , , , , , , ,

Leave a Reply