Auditing User Permissions in SQL Server Databases: A Comprehensive Tutorial

Introduction

In any enterprise environment, maintaining security and ensuring that users have appropriate access levels to database resources is crucial. Auditing user permissions helps administrators understand who has what level of access within a database and serves as an important tool for compliance and security management. This tutorial will guide you through the process of creating SQL queries in Microsoft SQL Server to report on all user permissions within a database, covering direct permissions, role-based permissions, and permissions inherited from the public role.

Understanding SQL Server Security

Before diving into query creation, it’s essential to understand some fundamental concepts related to SQL Server security:

1. Principals: These are entities that can be granted access rights. Principals include SQL users, Windows users, roles, and groups.
2. Permissions: Access rights that principals can have on database objects like tables, views, and stored procedures.
3. Roles: A collection of permissions assigned to a group of principals.

Querying Direct Permissions

Direct permissions are those granted explicitly to individual user accounts or Windows logins. These can be queried using the sys.database_principals system catalog view along with sys.database_permissions.

SQL Script for Direct Permissions

SELECT  
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[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 princ  
LEFT JOIN
    sys.login_token ulogin ON princ.[sid] = ulogin.[sid]
LEFT JOIN        
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[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 
    princ.[type] IN ('S', 'U')

Querying Role-Based Permissions

Users can also inherit permissions through roles they are members of. These role-based permissions can be found by joining the sys.database_role_members with sys.database_principals.

SQL Script for Role-Based Permissions

SELECT  
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[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 members
JOIN
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    sys.login_token ulogin ON memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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]

Querying Permissions for the Public Role

The public role is a special database-level principal that every user implicitly has. Permissions granted to this role are available to all users unless overridden.

SQL Script for Public Role Permissions

SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[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 roleprinc
LEFT JOIN        
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[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
    roleprinc.[type] = 'R' AND
    roleprinc.[name] = 'public' AND
    obj.is_ms_shipped = 0

Consolidating the Queries

For a comprehensive report, combine all these queries using UNION ALL to generate a complete list of permissions across direct users, roles, and the public role.

-- Combine all permission queries here with UNION ALL for a full audit report
<Direct Permissions Query>
UNION ALL
<Role-Based Permissions Query>
UNION ALL
<Public Role Permissions Query>
ORDER BY [UserName], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]

Conclusion

This tutorial has demonstrated how to construct SQL queries that can audit and report on user permissions in a SQL Server database. The ability to generate such reports is invaluable for administrators seeking to ensure proper access control, comply with security policies, and maintain an overall secure environment.

Best Practices

• Regularly review permission settings as part of your organization’s security policy.
• Restrict the use of high-level permissions like CONTROL or ALTER.
• Implement least privilege principle: grant users only the permissions they need to perform their tasks.

Tips for Further Exploration

• Consider automating the generation and distribution of these reports using SQL Server Agent Jobs.
• Explore advanced security features in newer versions of SQL Server, such as Row-Level Security (RLS).
• For environments with a high number of principals or complex permission structures, consider performance optimization techniques when running these queries.