SQL Server query to find all permissions/access for all users in a database

SQL Server query to find all permissions/access for all users in a database

To find all permissions and access for all users in a SQL Server database, you can use the following query. This query retrieves information from various system tables in the database to provide a comprehensive list of permissions for each user:

SELECT prin.name AS [Principal Name], perm.permission_name AS [Permission], perm.state_desc AS [Permission State], obj.type_desc AS [Object Type], obj.name AS [Object Name], col.name AS [Column Name] FROM sys.database_permissions perm JOIN sys.database_principals prin ON perm.grantee_principal_id = prin.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id LEFT JOIN sys.columns col ON perm.major_id = col.object_id AND perm.minor_id = col.column_id WHERE prin.type_desc IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP', 'APPLICATION_ROLE') ORDER BY prin.name, obj.name, col.name;

Explanation:

  • The query joins the sys.database_permissions, sys.database_principals, sys.objects, and sys.columns system tables to gather information about permissions.
  • It selects relevant columns such as principal name, permission name, permission state, object type, object name, and column name.
  • The WHERE clause filters results for users (prin.type_desc includes 'SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP', 'APPLICATION_ROLE').
  • The result is ordered by principal name, object name, and column name for clarity.

This query provides a detailed overview of permissions for each user in the specified database. You may need appropriate permissions to execute this query.

Please replace your_database with the actual name of your database in the USE statement before running the query.

Examples

  1. SQL Server list all user permissions in a database: Find all permissions and access for all users in a SQL Server database.

    SELECT princ.name AS [User], perm.permission_name, perm.state_desc AS [Permission State], obj.name AS [Object Name], obj.type_desc AS [Object Type] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id

  2. SQL Server list user permissions on a specific table: Narrow down permissions to a specific table in a SQL Server database.

    SELECT princ.name AS [User], perm.permission_name, perm.state_desc AS [Permission State] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id WHERE perm.major_id = OBJECT_ID('your_table_name');

  3. SQL Server find all database roles and permissions: Retrieve a comprehensive list of database roles and their associated permissions.

    SELECT role.name AS [Role], princ.name AS [User], perm.permission_name, perm.state_desc AS [Permission State] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.database_role_members role_members ON princ.principal_id = role_members.member_principal_id LEFT JOIN sys.database_principals role ON role_members.role_principal_id = role.principal_id;

  4. SQL Server find specific user permissions: Identify permissions for a specific user in a SQL Server database.

    SELECT princ.name AS [User], perm.permission_name, perm.state_desc AS [Permission State], obj.name AS [Object Name], obj.type_desc AS [Object Type] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id WHERE princ.name = 'your_username';

  5. SQL Server list all users and their database roles: Retrieve a comprehensive list of all users in a SQL Server database along with their associated roles.

    SELECT princ.name AS [User], role.name AS [Role] FROM sys.database_role_members role_members INNER JOIN sys.database_principals princ ON role_members.member_principal_id = princ.principal_id LEFT JOIN sys.database_principals role ON role_members.role_principal_id = role.principal_id;

  6. SQL Server query to find all server-level permissions: Explore server-level permissions for all users in a SQL Server instance.

    SELECT princ.name AS [Login/User], perm.permission_name, perm.state_desc AS [Permission State] FROM sys.server_permissions perm INNER JOIN sys.server_principals princ ON perm.grantee_principal_id = princ.principal_id;

  7. SQL Server find user's role membership: Identify the roles to which a specific user belongs in a SQL Server database.

    SELECT princ.name AS [User], role.name AS [Role] FROM sys.database_role_members role_members INNER JOIN sys.database_principals princ ON role_members.member_principal_id = princ.principal_id LEFT JOIN sys.database_principals role ON role_members.role_principal_id = role.principal_id WHERE princ.name = 'your_username';

  8. SQL Server query to find all users with explicit permissions: Find users in a SQL Server database with explicit permissions.

    SELECT princ.name AS [User], perm.permission_name, perm.state_desc AS [Permission State], obj.name AS [Object Name], obj.type_desc AS [Object Type] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id WHERE perm.class_desc = 'OBJECT_OR_COLUMN';

  9. SQL Server find users without specific permission: Identify users who do not have a specific permission in a SQL Server database.

    SELECT princ.name AS [User], perm.permission_name, perm.state_desc AS [Permission State], obj.name AS [Object Name], obj.type_desc AS [Object Type] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id WHERE perm.permission_name != 'your_permission';

  10. SQL Server query to find all users with execute permission: Identify users with execute permission on stored procedures in a SQL Server database.

    SELECT princ.name AS [User], perm.state_desc AS [Permission State], obj.name AS [Stored Procedure] FROM sys.database_permissions perm INNER JOIN sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id WHERE perm.permission_name = 'EXECUTE';

More Tags

curl audio-player prediction copy word-count uigesturerecognizer cakephp-2.1 service-worker android-phone-call xamarin.mac

More Programming Questions

More Auto Calculators

More Housing Building Calculators

More Various Measurements Units Calculators

More Retirement Calculators