Find All Securable Objects in SQL Server and Permissions

Problem

For a current project, I need to know all the permission available in SQL Server. Does SQL Server have a built-in solution to provide a permissions hierarchy for any specific object, database, or instance?

Solution

SQL Server offers a system function sys.fn_builtin_permissions to list all permissions on a securable class, including any database object, database, schema, or SQL Server instance. To learn more about permissions assigned to a specific login/principle, take a look at this article: List SQL Server Login and User Permissions with fn_my_permissions.

Getting Started with sys.fn_builtin_permissions

Let’s start with looking at the syntax for sys.fn_builtin_permissions.

sys.fn_builtin_permissions ( [ DEFAULT | NULL ]  
    | empty_string | '<securable_class>' } )  
  
<securable_class> ::=   
      APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY | AVAILABILITY GROUP  
    | CERTIFICATE | CONTRACT | DATABASE | DATABASE SCOPED CREDENTIAL    
    | ENDPOINT | FULLTEXT CATALOG | FULLTEXT STOPLIST | LOGIN      
    | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE | ROUTE    
    | SCHEMA | SEARCH PROPERTY LIST | SERVER | SERVER ROLE | SERVICE    
    | SYMMETRIC KEY | TYPE | USER | XML SCHEMA COLLECTION

DEFAULT will return a list of all permissions for all securable classes. NULL will also return a similar result set as the DEFAULT argument. If we have an empty_string, the result set will be the same as DEFAULT.

For the securable_class, this will return all permissions related to that specific securable. You can see the list of all securable classes below.

APPLICATION ROLE
ASSEMBLY
ASYMMETRIC KEY
AVAILABILITY GROUP
CERTIFICATE
CONTRACT
DATABASE
DATABASE SCOPED CREDENTIAL
ENDPOINT
FULLTEXT CATALOG
FULLTEXT STOPLIST
LOGIN
MESSAGE TYPE
OBJECT
REMOTE SERVICE BINDING
ROLE
ROUTE
SCHEMA
SEARCH PROPERTY LIST
SERVER
SERVER ROLE
SERVICE
SYMMETRIC KEY
TYPE
USER
XML SCHEMA COLLECTION

This function will return no result if you specify an invalid securable class.

List All Permissions with sys.fn_builtin_permissions

Below is a simple statement. It uses DEFAULT to display all permissions and details for all valid securable classes.

SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

The result set was very big so it was not possible to capture one screenshot. There are a total of 248 permissions for various securable classes.

sys.fn_builtin_permissions(DEFAULT) List all permissions

Again, empty_string and NULL will return the same result as DEFAULT.

SELECT * FROM sys.fn_builtin_permissions('');
SELECT * FROM sys.fn_builtin_permissions(NULL);

Here we can get a count of the permissions.

SELECT COUNT(*) AS [ALL Permissions] FROM sys.fn_builtin_permissions('');
SELECT COUNT(*) AS [ALL Permissions using NULL] FROM sys.fn_builtin_permissions(NULL);

We can see there are 248 rows for each.

sys.fn_builtin_permissions empty_string and NULL

Out of these 248 permissions, you can see the unique number of securable classes and the unique number of permission names using the following statement.

SELECT COUNT(DISTINCT class_desc)      AS [Number of unique securable class],
       COUNT(DISTINCT permission_name) AS [Number of unique Permissions]
FROM sys.fn_builtin_permissions(DEFAULT)

This shows 27 unique securable classes and 122 unique permission names.

Unique values for securable class and permission names

You can explore the names of all unique securable classes and their respective permission names. To do this, use a GROUP BY statement with this system function as follows.

SELECT class_desc, COUNT(class_desc) AS [Number of Permissions]
FROM sys.fn_builtin_permissions(DEFAULT)
GROUP BY class_desc

The result includes all unique securable classes along with the total number of permissions for each securable class. For example, row 14 is securable class OBJECT and its number of permissions is 12. This means there are 12 different types of permissions that can be assigned to securable class OBJECT in SQL Server. Similarly, row 19 is securable class DATABASE which has 82 types of permissions that can be assigned on securable class DATABASE.

Unique securable class with total number of permissions

Now, let’s see what permissions we can assign to the OBJECT securable class.

SELECT * FROM sys.fn_builtin_permissions(N'OBJECT');

The column class_desc displays the securable class OBJECT, and the permission_name column shows the 12 permissions we can assign to OBJECT.

class_desc displaying securable class OBJECT and permission_name column showing 12 permissions

You can also get similar results using a WHERE clause with this system function.

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)
WHERE class_desc = 'OBJECT'

Here are the results.

Here are a couple more examples.

SELECT * FROM sys.fn_builtin_permissions(N'AVAILABILITY GROUP');
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) WHERE class_desc = 'LOGIN'

Here is the output.

All permissions for securable class â€œAVAILABILITY GROUPâ€. All permissions for securable class LOGIN

Let’s look at it from the permission_name perspective where we want to find all permissions that use ALTER.

SELECT * FROM sys.fn_builtin_permissions(DEFAULT) WHERE permission_name = 'ALTER';

The output below shows there are 25 rows returned.

25 securable class can execute ALTER statement

Here is another example use SELECT as the permission_name.

SELECT * FROM sys.fn_builtin_permissions(DEFAULT) WHERE permission_name = 'SELECT';

Here are the results.

Run SELECT statement on 3 securable class

Next Steps

Take a look at these other security-related articles:

Resources

Community

Subscribe