SQL Server Query to Find All Permissions/Access for All Users in a Database

In SQL databases, managing and auditing user permissions is crucial for ensuring security and proper access control. To find out what permissions or access levels are granted to users within a database, we can use built-in functions such as sys.fn_my_permissions and sys.fn_builtin_permissions.

These functions help administrators and database managers to retrieve detailed information about user rights and permissions. In this article, we will learn various methods to find all permissions/access for all users in a database in detail.

How to Find All Permissions/Access for All Users in a Database

To find the permissions of all users in a database, use sys.fn_my_permissions and sys.fn_builtin_permissions functions.

Let's discuss each of these methods in detail below.

1. View User Permissions using the sys.fn_my_permissions function

The sys.fn_my_permissions method provides a list of server-level permissions granted to users within the database. We use the built-in function SYS.FN_MY_PERMISSIONS to display permissions for the current user (using the MY keyword) or any other user.

The keyword ‘SERVER’ indicates that the function lists all server-level permissions granted by default.

Query:

SELECT * FROM FN_MY_PERMISSIONS
(NULL, 'SERVER');

Output:

2. View User Permissions using sys.fn_builtin_permissions function

The sys.fn_builtin_permissions function is used to list out all the securable classes and their respective permissions which lies under the default list of permissions. There are a total of 27 securable classes present in SQL.

Query:

SELECT DISTINCT CLASS_DESC 
FROM SYS.FN_BUILTIN_PERMISSIONS(DEFAULT);

Output:

We can also use an extended form of the above method. It lists all the permission names, the type, the parent class and the controlling server for all the permissions. Here, the parent class is SERVER for all permissions.

Query:

SELECT * FROM SYS.FN_BUILTIN
_PERMISSIONS(DEFAULT);

Output:

Example

Here, we use a database of our own i.e. GEEKSFORGEEKS. So, we start using this database using the keyword USE. Then, we display all the permissions of all users in this database using SYS.FN_BUILTIN_PERMISSIONS(DEFAULT) command. All 34 roles of the entity server are displayed as a result. 

Query:

USE GEEKSFORGEEKS
SELECT * M SYS.FN_BUILTIN_
PERMISSIONS(DEFAULT);

Output:

Conclusion

Understanding user permissions and access rights is essential for maintaining database security and compliance. By utilizing functions like sys.fn_my_permissions and sys.fn_builtin_permissions, you can easily view and manage the permissions granted to users across the database.