SQL Server User Permissions and Role Memberships

calendar Apr 10, 2026 / · 11 min read · sql server security permissions sql scripts database administration sys.database_principals sys.database_permissions sys.database_role_members sys.server_principals sys.login_token role memberships security audit sys.columns sys.objects  ·
Share on: linkedin copy

A single catalog query can list every grant, deny, and role membership in a database, and the result is the starting point for an access review. The script below returns one flat result set that covers permissions granted directly to a user, permissions a user holds through a database role, and permissions granted to the public role.

Purpose and Overview

Who can read the SensitiveTable object, and do they get that access through a direct grant, a role, or public? Answering that from SSMS means opening the properties of every user and every role. The catalog answers it in one pass, because database permissions live in sys.database_permissions, principals live in sys.database_principals, and role membership lives in sys.database_role_members.

Permissions in SQL Server come from more than one place. A database user can be granted a permission directly. A user can be a member of a database role that holds the permission. And every database user belongs to the public database role: when a user has not been granted or denied a permission on a securable, the user inherits the permissions granted to public on that securable. The script reads all three paths and labels each row, so a reviewer can see why a user has an access, not only that they have it.

The output has one row per principal, permission, and object or column. The reviewer looks for grants made directly to users that should have gone through a role, DENY entries that explain a permission error, and public grants that open objects to everyone. The columns are described at the end of the Code Breakdown section.

Code Breakdown

Run the script in the context of the database you want to audit. The catalog views it reads are scoped to the current database. The script lists direct database user permissions, role-based permissions, and public permissions as three SELECT statements joined by UNION.

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

First SELECT: permissions granted directly to users

The first SELECT starts from sys.database_principals, which returns one row per security principal in the database. The filter type IN ('S', 'U', 'G') keeps SQL users (S), Windows users (U), and Windows groups (G). sys.database_principals defines further types, such as certificate-mapped users, external Microsoft Entra users, and database roles; add their letters to the filter if your database has them.

It then LEFT JOINs sys.database_permissions on grantee_principal_id = principal_id. A LEFT JOIN keeps principals that hold no explicit permission, so they still appear in the report with empty permission columns. The Role column is NULL because nothing here passes through a role.

sys.database_permissions returns a row for every permission or column-exception permission in the database. The class column says what kind of thing the permission is on, and what major_id means depends on that class: for object or column permissions (class 1) it is the object ID, for schema permissions (class 3) it is the schema ID. The script therefore resolves major_id with OBJECT_NAME only for class 1 and with SCHEMA_NAME for class 3, and it joins sys.objects and sys.columns only for class 1. Without that guard, a schema ID or a database-level major_id of 0 would be looked up as an object ID and could show an unrelated object name. For a column permission, minor_id holds the column ID; it is 0 for an object-level grant, which matches no row in sys.columns.

Resolving the login with sys.server_principals

The LoginName column joins sys.database_principals to sys.server_principals on sid. A database user's sid is the security identifier of the principal, and for a Windows principal the server principal's SID matches the Windows SID. A database user with no matching server principal gets a NULL in LoginName.

The COLLATE DATABASE_DEFAULT clause is there because of a collation rule: identifiers of instance-level objects, such as logins, take the default collation of the instance, while identifiers of objects inside a database take the database collation. The clause converts the login name to the database collation, so the column lines up with the other database-level text columns in the UNION.

sys.login_token is the wrong view for this job. It returns one row for every server principal that is part of the login token of the session running the query, so it identifies the caller, not every user in the database; joined to other users, it finds nothing.

Second SELECT: permissions held through a role

The second SELECT starts from sys.database_role_members, which returns one row for each member of each database role. Database users, application roles, and other database roles can all be members. The query joins sys.database_principals twice, once as roleprinc for the role and once as memberprinc for the member, then joins sys.database_permissions on the role's principal ID. The permission columns describe what the role holds, and the member inherits it by membership.

Role membership is not expanded recursively. If a member is itself a role, its row appears with UserType of DATABASE_ROLE, and the users inside that nested role do not appear under the parent role. Adding user-defined roles as members of fixed roles is discouraged because it can enable unintended privilege escalation, so a nested-role row is worth a second look.

Where permissions are granted through roles, a report that only reads direct grants to users misses that access.

Third SELECT: the public role

Every database user belongs to the public database role, so the third SELECT reports grants made to it, using {All Users} as a label. It uses an inner join to sys.objects and keeps is_ms_shipped = 0 so the long list of default grants on system objects stays out. Many permissions are assigned to the public roles by default, and most of them are needed for routine operations. What this branch shows you is what was added on top for your own objects.

Output columns

ColumnDescription
DatabaseUserNameThe database principal name
UserTypeThe principal's type_desc, such as SQL_USER, WINDOWS_USER, WINDOWS_GROUP, or DATABASE_ROLE
LoginNameThe server principal with the same SID; NULL if none
RoleThe role through which the permission is held; NULL for direct grants
PermissionClassWhat the permission is on, such as DATABASE, OBJECT_OR_COLUMN, or SCHEMA
PermissionTypeThe permission name, such as SELECT, EXECUTE, ALTER, or CONTROL
PermissionStateDENY, REVOKE, GRANT, or GRANT_WITH_GRANT_OPTION
ObjectTypeThe type_desc from sys.objects, such as USER_TABLE
ObjectNameThe object or schema name
ColumnNameThe column for column-level permissions; NULL otherwise

Key Benefits and Use Cases

  • Three access paths in one result: direct grants, role-based grants, and public grants come back together, so a reviewer does not have to merge three reports.
  • Column-level permissions are visible: column-level permissions override object-level permissions on the same entity, and the ColumnName column shows them.
  • All permission states are shown: DENY entries appear next to GRANT entries. DENY always supersedes GRANT, which makes the DENY rows the first place to look when a user reports an unexpected permission error.
  • Column exceptions are labelled: a GRANT on a table followed by a REVOKE on one column shows up as one GRANT row and one REVOKE row, so the exception is not hidden.
  • Public grants stand out: any object where public holds a permission is listed under {All Users}.
  • Easy export: the flat output copies into a spreadsheet for an access review or compliance sign-off.

Performance Considerations

  • Current database only: the catalog views are scoped to the database you are connected to. To audit several databases, run the script in each one.
  • Fixed database roles are incomplete here: the permissions of fixed database roles do not appear in sys.database_permissions. For db_datareader, db_owner, and the other fixed roles, the role-based branch shows the membership but the permission columns are NULL. Their permissions are fixed by the role definition: for example, db_datareader members can read all data from all user tables and views.
  • Metadata visibility limits the result: any user can see their own permissions, but seeing other users' permissions needs VIEW DEFINITION, ALTER ANY USER, or a permission on the user, and VIEW SECURITY DEFINITION on the database applies to SQL Server 2022 and later. Catalog view visibility is also limited to securables a user owns or has some permission on. A low-privilege account can see a shorter result than an administrator.
  • Server-level access is separate: this script covers database permissions only. Server-level permissions are held at the server level; server-level permissions cannot be granted to database roles, and logins and server roles cannot be added to database roles.
  • Output size: a database with many users and objects produces a large result. Add a WHERE clause on princ.[name] in the first branch, or on the object name, to narrow it.

Practical Tips

  • Review DENY rows first: filter on PermissionState = 'DENY' when someone reports a permission error that their grants should have prevented.
  • Look past GRANT and DENY: REVOKE rows mark column exceptions, and GRANT_WITH_GRANT_OPTION rows mark grants that carry the grant option.
  • Filter after the union: to see one user or one table, put the three SELECT statements in a derived table without the ORDER BY, then filter and sort in the outer query:
1SELECT *
2FROM (
3    -- the three SELECT statements joined by UNION, without ORDER BY
4) AS access_report
5WHERE [DatabaseUserName] = N'AppUser'
6ORDER BY [ObjectName], [PermissionType];
  • Keep a baseline: write the output to an audit table on a schedule, such as a weekly SQL Server Agent job, and compare each week with the last to find grants nobody planned.
  • Include more principal types when needed: add 'E', 'X', 'C', or 'K' to the first branch's type filter if the database uses Microsoft Entra users, certificate-mapped users, or key-mapped users.

Conclusion

The three-branch UNION gives a database-level access report from the system catalog alone: direct grants, role-based grants, and public grants, each labelled with its state and the object or column it applies to. Run it per database, remember that fixed-role permissions are defined outside sys.database_permissions, and compare each run with the last one so changes to access are visible when they happen.

References

Posts in this series