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

There are multiple ways to list all the permissions for users present in a Database in Oracle SQL. Such ways are depicted in the below article. For this article, we will be using the Microsoft SQL Server as our database.

Method 1: This method lists all the server level permissions granted to the user by the database. Here we are using the inbuilt function called SYS.FN_MY_PERMISSIONS which is used to display the permissions for the current user(MY keyword) and even any other user. Here, the keyword ‘SERVER’ denotes that the function shall list out all the server level permissions granted to the users by default.

Query:

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

Output:

Method 2: Next we see how to list out all the securable classes which are present in SQL. These all classes and their respective permissions also lie under the default list of permissions. There are a total of 27 securable classes present in SQL. All are listed below.

Query:

SELECT DISTINCT CLASS_DESC FROM 
SYS.FN_BUILTIN_PERMISSIONS(DEFAULT);

Output:

Method 3: This method is just 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:

Method 4: 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 * FROM SYS.FN_BUILTIN_
PERMISSIONS(DEFAULT);

Output: