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
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:
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:
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.
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.