How to use fn_builtin_permissions SQL Server Security Function

By: Manvendra Singh   |   Updated: 2022-08-02   |   Comments   |   Related: > Security

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.

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.

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.

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.

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.

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.

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.

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.

Next Steps

Take a look at these other security-related articles:

• SQL Server Permissions Granted to All Users By Default
• SQL Server Security Tips

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips