A Comprehensive Analysis of SQL Server User Permission Auditing Queries

Introduction

In database management, ensuring proper access controls is crucial for security. SQL Server provides a robust permission system, but auditing these permissions can be complex. This article presents a comprehensive query method to list all user permissions in a database, aiding in security audits. Permission auditing helps identify potential risks, ensure compliance, and prevent unauthorized access.

Understanding SQL Server Permissions

SQL Server permissions are managed through principals, securables, and permissions. Principals include users, roles, and groups, and permissions can be granted directly or through roles. System views such as sys.database_principals and sys.database_permissions provide access to permission details, forming the foundation for effective auditing queries.

Core Auditing Query Structure

The core query, based on community best practices, combines three parts to cover all permission scenarios: direct permissions granted to users, permissions through database roles, and default permissions of the public role. It uses the UNION operator to merge results, ensuring comprehensiveness. Below is a rewritten version of the query for improved readability and understanding.

-- SQL Server Security Audit Query
-- Part 1: Direct permissions to users
SELECT 
    UserName = CASE p.type 
                  WHEN 'S' THEN p.name
                  WHEN 'U' THEN l.name COLLATE Latin1_General_CI_AI
               END,
    UserType = CASE p.type 
                  WHEN 'S' THEN 'SQL User'
                  WHEN 'U' THEN 'Windows User'
               END,
    DatabaseUserName = p.name,
    Role = NULL,
    PermissionType = perm.permission_name,
    PermissionState = perm.state_desc,
    ObjectType = obj.type_desc,
    ObjectName = OBJECT_NAME(perm.major_id),
    ColumnName = col.name
FROM 
    sys.database_principals p
LEFT JOIN 
    sys.server_principals l ON p.sid = l.sid
LEFT JOIN 
    sys.database_permissions perm ON perm.grantee_principal_id = p.principal_id
LEFT JOIN 
    sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN 
    sys.objects obj ON perm.major_id = obj.object_id
WHERE 
    p.type IN ('S', 'U')

UNION

-- Part 2: Permissions through roles
SELECT 
    UserName = CASE m.type 
                  WHEN 'S' THEN m.name
                  WHEN 'U' THEN l.name COLLATE Latin1_General_CI_AI
               END,
    UserType = CASE m.type 
                  WHEN 'S' THEN 'SQL User'
                  WHEN 'U' THEN 'Windows User'
               END,
    DatabaseUserName = m.name,
    Role = r.name,
    PermissionType = perm.permission_name,
    PermissionState = perm.state_desc,
    ObjectType = obj.type_desc,
    ObjectName = OBJECT_NAME(perm.major_id),
    ColumnName = col.name
FROM 
    sys.database_role_members rm
JOIN 
    sys.database_principals r ON r.principal_id = rm.role_principal_id
JOIN 
    sys.database_principals m ON m.principal_id = rm.member_principal_id
LEFT JOIN 
    sys.server_principals l ON m.sid = l.sid
LEFT JOIN 
    sys.database_permissions perm ON perm.grantee_principal_id = r.principal_id
LEFT JOIN 
    sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN 
    sys.objects obj ON perm.major_id = obj.object_id

UNION

-- Part 3: Permissions for public role
SELECT 
    UserName = '{All Users}',
    UserType = '{All Users}',
    DatabaseUserName = '{All Users}',
    Role = r.name,
    PermissionType = perm.permission_name,
    PermissionState = perm.state_desc,
    ObjectType = obj.type_desc,
    ObjectName = OBJECT_NAME(perm.major_id),
    ColumnName = col.name
FROM 
    sys.database_principals r
LEFT JOIN 
    sys.database_permissions perm ON perm.grantee_principal_id = r.principal_id
LEFT JOIN 
    sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id
JOIN 
    sys.objects obj ON obj.object_id = perm.major_id
WHERE 
    r.type = 'R' AND r.name = 'public' AND obj.is_ms_shipped = 0
ORDER BY 
    UserName, ObjectName, ColumnName, PermissionType, PermissionState, ObjectType;

This query merges results from three parts using the UNION operator, ensuring coverage of all permission types. Each part uses left joins to handle potentially null permission data, preventing loss of information.

Step-by-Step Code Explanation

Part 1: Direct Permissions – This section queries sys.database_principals for users (types 'S' for SQL users and 'U' for Windows users) and joins with sys.database_permissions to retrieve directly granted permissions. Connections to sys.columns and sys.objects handle column-level and object-level permissions, ensuring the output includes permission type, state, and object details.

Part 2: Role-Based Permissions – It uses sys.database_role_members to find role memberships and then accesses permissions through sys.database_permissions. This ensures that users inherit access even without direct grants.

Part 3: Public Role Permissions – The public role provides default permissions for all users; this part lists them while excluding Microsoft system objects (obj.is_ms_shipped = 0) to focus on user-defined objects.

Enhancements and Best Practices

Based on other answers and reference articles, the query can be further optimized. For instance, handling Windows groups (type 'G') to cover more principal types; using sys.server_principals instead of sys.login_token for broader login account coverage; excluding system accounts like 'sys' and 'INFORMATION_SCHEMA' to reduce noise; and improving ObjectType handling with perm.class_desc to distinguish object categories such as schemas or impersonations. Additionally, reference articles suggest using dynamic SQL or loops for multi-database audits or checking user enablement status via sys.server_principals' is_disabled field. These enhancements improve the query's robustness and applicability.

Practical Application

To use this query, execute it in the target database. The output can be used to generate audit reports, helping identify over-provisioned or missing permissions. It is advisable to test the query in a development environment first to ensure accuracy. For multi-database scenarios, extend the query with dynamic SQL or stored procedures like sp_msforeachdb, as shown in reference articles. Regular execution of such audits aids in maintaining security policies and compliance requirements.

Conclusion

User permission auditing is a cornerstone of database security. The query provided in this article serves as a foundational tool that can be customized based on specific needs. By incorporating community improvements and practical experiences, database administrators can more effectively monitor and optimize access controls, reducing security risks. Continuous auditing and updating of permission policies are essential steps for ensuring data protection.