SQL Server User Permissions and Role Memberships

Audit SQL Server Database User Permissions and Role Memberships

This script queries the SQL Server system catalog to produce a complete audit of database-level permissions, covering direct object grants to users, permissions inherited through database roles, and permissions granted to the public role — all in a single result set.

Purpose and Overview

Understanding who has access to what in a SQL Server database is a fundamental security and compliance requirement. Permissions in SQL Server are layered: a user may have rights granted directly, through membership in one or more database roles, or through the public role that every user belongs to by default. Auditing all three layers individually is time-consuming. This script, from the DBA-Scripts collection by Bulent Gucuk, uses three UNION-connected queries against the system catalog views to produce one flat result set covering every permission pathway for every user. Security teams can use the output for access reviews, compliance audits, and troubleshooting unexpected permission grants or denials.

Code Breakdown

The complete script is shown below, followed by an explanation of each section. Run this in the context of the database you want to audit.

 1-- List all access provisioned to a SQL user or Windows user/group directly
 2SELECT
 3    [UserName] = CASE princ.[type]
 4                    WHEN 'S' THEN princ.[name]
 5                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
 6                 END,
 7    [UserType] = CASE princ.[type]
 8                    WHEN 'S' THEN 'SQL User'
 9                    WHEN 'U' THEN 'Windows User'
10                 END,
11    [DatabaseUserName] = princ.[name],
12    [Role]             = NULL,
13    [PermissionType]   = perm.[permission_name],
14    [PermissionState]  = perm.[state_desc],
15    [ObjectType]       = obj.type_desc,
16    [ObjectName]       = OBJECT_NAME(perm.major_id),
17    [ColumnName]       = col.[name]
18FROM sys.database_principals AS princ
19LEFT JOIN sys.login_token AS ulogin
20    ON princ.[sid] = ulogin.[sid]
21LEFT JOIN sys.database_permissions AS perm
22    ON perm.[grantee_principal_id] = princ.[principal_id]
23LEFT JOIN sys.columns AS col
24    ON col.[object_id] = perm.major_id
25   AND col.[column_id] = perm.[minor_id]
26LEFT JOIN sys.objects AS obj
27    ON perm.[major_id] = obj.[object_id]
28WHERE princ.[type] IN ('S', 'U')
29
30UNION
31
32-- List all access provisioned through a database or application role
33SELECT
34    [UserName] = CASE memberprinc.[type]
35                    WHEN 'S' THEN memberprinc.[name]
36                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
37                 END,
38    [UserType] = CASE memberprinc.[type]
39                    WHEN 'S' THEN 'SQL User'
40                    WHEN 'U' THEN 'Windows User'
41                 END,
42    [DatabaseUserName] = memberprinc.[name],
43    [Role]             = roleprinc.[name],
44    [PermissionType]   = perm.[permission_name],
45    [PermissionState]  = perm.[state_desc],
46    [ObjectType]       = obj.type_desc,
47    [ObjectName]       = OBJECT_NAME(perm.major_id),
48    [ColumnName]       = col.[name]
49FROM sys.database_role_members AS members
50JOIN sys.database_principals AS roleprinc
51    ON roleprinc.[principal_id] = members.[role_principal_id]
52JOIN sys.database_principals AS memberprinc
53    ON memberprinc.[principal_id] = members.[member_principal_id]
54LEFT JOIN sys.login_token AS ulogin
55    ON memberprinc.[sid] = ulogin.[sid]
56LEFT JOIN sys.database_permissions AS perm
57    ON perm.[grantee_principal_id] = roleprinc.[principal_id]
58LEFT JOIN sys.columns AS col
59    ON col.[object_id] = perm.major_id
60   AND col.[column_id] = perm.[minor_id]
61LEFT JOIN sys.objects AS obj
62    ON perm.[major_id] = obj.[object_id]
63
64UNION
65
66-- List all access provisioned to the public role
67SELECT
68    [UserName]         = '{All Users}',
69    [UserType]         = '{All Users}',
70    [DatabaseUserName] = '{All Users}',
71    [Role]             = roleprinc.[name],
72    [PermissionType]   = perm.[permission_name],
73    [PermissionState]  = perm.[state_desc],
74    [ObjectType]       = obj.type_desc,
75    [ObjectName]       = OBJECT_NAME(perm.major_id),
76    [ColumnName]       = col.[name]
77FROM sys.database_principals AS roleprinc
78LEFT JOIN sys.database_permissions AS perm
79    ON perm.[grantee_principal_id] = roleprinc.[principal_id]
80LEFT JOIN sys.columns AS col
81    ON col.[object_id] = perm.major_id
82   AND col.[column_id] = perm.[minor_id]
83JOIN sys.objects AS obj
84    ON obj.[object_id] = perm.[major_id]
85WHERE roleprinc.[type] = 'R'
86    AND roleprinc.[name] = 'public'
87    AND obj.is_ms_shipped = 0
88ORDER BY
89    [UserName],
90    [ObjectName],
91    [ColumnName],
92    [PermissionType],
93    [PermissionState],
94    [ObjectType];

First UNION: Direct User Permissions

The first SELECT reports permissions granted directly to individual SQL Server users (type = 'S') and Windows users or groups (type = 'U'). It reads from sys.database_principals for the user list and LEFT JOINs to sys.database_permissions on grantee_principal_id. The Role column is returned as NULL because these grants are not mediated by a role.

sys.login_token provides the Windows login name for Windows-authenticated users. The COLLATE Latin1_General_CI_AI clause normalizes the collation of the Windows name so it compares correctly regardless of the server or database collation.

LEFT JOINs to sys.columns and sys.objects retrieve column-level permission details and the object type description. When a GRANT is on a table column rather than the entire object, minor_id in sys.database_permissions holds the column ID.

Second UNION: Role-Based Permissions

The second SELECT surfaces permissions that users inherit through database role membership. It starts from sys.database_role_members, which maps each role to its members. Joining sys.database_principals twice — once as roleprinc (the role) and once as memberprinc (the member) — gives both the role name and the member user's identity. The permission columns then reflect what the role itself has been granted, which the member user inherits by membership.

This section is important because many environments assign permissions exclusively through roles such as db_datareader or custom application roles, and direct permission grants to users are rare. If you only query direct grants, you miss the majority of access rights in these environments.

Third UNION: Public Role Permissions

Every database user is automatically a member of the public role. Any permissions granted to public apply to all users unless explicitly denied. The third SELECT reports those public-role grants, filtering to non-Microsoft-shipped objects (obj.is_ms_shipped = 0) to avoid cluttering the output with system object entries. The UserName and UserType columns return {All Users} as a visual indicator that these permissions apply to everyone.

Output Columns

Key Benefits and Use Cases

• Covers all three permission pathways — direct grants, role-based grants, and public role — in one query
• Identifies column-level permissions which are easy to miss with standard SSMS views
• Shows both GRANT and DENY states so conflicting permissions are visible
• Windows users and SQL users are clearly labeled in the UserType column
• Public role section immediately reveals any objects with permissions open to all users
• Output is easy to export to Excel for an access review spreadsheet

Performance Considerations

• Database context: Run the script while connected to the target database. The system catalog views — sys.database_principals, sys.database_permissions, sys.database_role_members — are database-scoped and return data for the current database only. To audit multiple databases, run the script once per database or wrap it in a loop using sp_MSforeachdb or a custom cursor.
• Server-level permissions: This script covers database-level permissions only. Server-level permissions (such as sysadmin, securityadmin, or server-scoped GRANT statements) are held in sys.server_principals and sys.server_permissions. Run a separate query against those views to audit server-level access.
• Large databases: On databases with many objects and users, the UNION output can be large. Filter by user or object to focus the result: add WHERE princ.[name] = 'specific_user' to the first SELECT, or add WHERE OBJECT_NAME(perm.major_id) = 'specific_table'.
• Fixed database roles: Built-in roles like db_owner and db_datareader have implicit permissions that are not stored in sys.database_permissions. The second UNION shows role membership but the PermissionType and PermissionState columns may be NULL for these roles because the permissions are defined in the engine, not in the catalog.

Practical Tips

To check a single user's effective permissions across all objects, filter the output:

1-- wrap the full query above as a CTE, then filter
2WITH perms AS (
3    -- paste full script here (excluding the ORDER BY)
4)
5SELECT *
6FROM perms
7WHERE UserName = 'domain\username'
8ORDER BY ObjectName, PermissionType;

To find all users with access to a specific table:

1-- filter the same CTE output
2WHERE ObjectName = 'SensitiveTable'
3ORDER BY UserName, Role;

For a compliance report showing only DENY entries — which override any GRANT and can be a source of mysterious access errors — filter on PermissionState = 'DENY'.

Schedule this script as a weekly SQL Server Agent job that writes results to an audit table. Compare the current week's output to last week's to detect any new permission grants that were not part of a planned change.

Conclusion

This three-part UNION query gives a complete picture of database access rights in SQL Server by combining direct user grants, role-inherited permissions, and public role access into a single auditable result set. It uses only standard system catalog views and requires no special tools or third-party utilities. Run it against any user database to produce the data needed for access reviews, compliance audits, and permission troubleshooting. Add filters for specific users or objects to focus the output, or wrap it in a scheduled job to maintain a rolling access audit trail.

References

• DBA-Scripts on GitHub by Bulent Gucuk — Source repository containing the security audit script adapted for this article
• Microsoft Docs: sys.database_permissions — Catalog view reference covering all permission types, states, and major/minor ID columns
• Microsoft Docs: sys.database_principals — Reference for the catalog view that holds all database-level user, role, and application role records
• Microsoft Docs: sys.database_role_members — Reference for the catalog view mapping database roles to their member principals