Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 272

The result will be as follows:

Class_desc Permission_name State_desc

SERVER CONNECT SQL GRANT

SERVER CREATE ANY

DATABASE

GRANT

SERVER SHUTDOWN DENY

SERVER VIEW ANY DATABASE GRANT_WITH_GRANT_OPTION

8. From the previous step, we have accomplished the steps to manage a securable. To

report on permissions for that securable use the Has_perms_by_name function as
follows:
USE AdventureWorks2008R2
GO
SELECT Has_perms_by_name ('AdventureWorks2008R2', 'DATABASE',
'ALTER')

9. The returned value will be either 0 or 1.0 meaning the current connection does not
have permission to ALTER and 1 means the current connection has permission to
ALTER the specified database.
10. Similarly, the Has_perms_by_name function can also be used to identify the
permissions on a database object for the current connection.
11. Finally, let us work on reporting the permissions for a principal by securable scope.
Execute the following TSQL statement to check the server-scoped permissions:
SELECT permission_name
FROM fn_my_permissions(NULL, N'SERVER')
ORDER BY permission_name

The securable_class will result in various levels of permissions
such as APPLICATION ROLE , ASSEMBLY , ASYMMETRIC KEY ,
CERTIFICATE , CONTRACT , DATABASE , ENDPOINT , FULLTEXT
CATALOG , LOGIN , MESSAGE TYPE , OBJECT , REMOTE SERVICE
BINDING , ROLE , ROUTE , SCHEMA , SERVER , SERVICE , SYMMETRIC
KEY , TYPE , USER , XML SCHEMA COLLECTION and so on.

12. The result set returns almost all the permissions names being the example executed
under context of the sysadmin privileges.

This completes the required steps to manage the server-level securable and database-level
permissions on a SQL Server instance.