Finding Permissions Granted Directly to Users Instead of Roles

Individual grants accumulate until an access review becomes hard to explain. Permissions granted directly to users are the usual source of that clutter. Inventory them first, then prepare a role-based change that preserves intended access instead of blindly replacing every entry with a GRANT.

Inventory Permissions Granted Directly to Users

sys.database_permissions records explicit database permissions. Joining sys.database_principals identifies whether the grantee is a user, group, or role. state_desc distinguishes GRANT, GRANT_WITH_GRANT_OPTION, DENY, and relevant revoke metadata. Keep those states visible instead of reporting only permission names.

I inspect the database's explicit permissions before proposing role cleanup. Role membership and implicit rights can add access not represented by a direct user row. The inventory is therefore an explicit-grant review, not a complete effective-permission calculation.

The following query includes common SQL, Windows, and external user or group principal types while excluding fixed system identities. It labels object and schema scopes and retains minor_id for column-level context. Metadata visibility can hide object names under a restricted account, so collect through the approved administrative context. Permissions are much easier to distribute than to explain later.

SELECT u.name AS Grantee,u.type_desc,p.state_desc,p.permission_name,p.class_desc,p.major_id,p.minor_id,
 CASE WHEN p.class=0 THEN DB_NAME()
 WHEN p.class=1 THEN QUOTENAME(OBJECT_SCHEMA_NAME(p.major_id))+N'.'+QUOTENAME(OBJECT_NAME(p.major_id))
 WHEN p.class=3 THEN QUOTENAME(SCHEMA_NAME(p.major_id))
 ELSE CONVERT(nvarchar(30),p.major_id) END AS SecurableName,
 CASE WHEN p.class=1 AND p.minor_id>0 THEN COL_NAME(p.major_id,p.minor_id) END AS ColumnName
FROM sys.database_permissions AS p
JOIN sys.database_principals AS u ON u.principal_id=p.grantee_principal_id
WHERE u.type IN('S','U','G','E','X') AND u.principal_id>4
ORDER BY u.name,p.class,p.major_id,p.minor_id,p.permission_name;

List permissions granted directly to users with their states and scopes before deciding which entries belong in shared roles.

Design Roles Around Shared Responsibilities

A custom database role should express a clear responsibility, such as reading an approved reporting area. Users with that responsibility become members, and the role receives the shared permissions. That makes the access model easier to review than many unrelated direct entries.

Do not group users solely because their current permission lists happen to match. Their responsibilities can differ, and the matching grants can be accidental. Confirm the intended access with the owner before treating the current state as the design authority.

I keep the role's purpose and membership decision beside its permission list. A role accumulating unrelated privileges eventually recreates the same review problem at a different level. Prefer a small number of meaningful roles without turning every individual exception into another permanent role. The goal is understandable responsibility-based access, with genuine exceptions still visible and justified.

Generate Narrow GRANT and REVOKE Candidates

The following generator targets plain object-level GRANT entries only. It excludes column permissions, DENY, grant-option entries, and other securable classes because those need separate handling. That scope makes the generated candidates understandable rather than pretending to solve every permission form.

The output contains membership, role grant, and later direct revoke statements for review. It does not execute them. Create the approved role first if needed, deduplicate repeated role grants, and order the real change so effective access is tested before redundant direct rights are removed.

What permission will the user retain after the revoke? Confirm that through the role and the actual execution context. A generated statement is not proof the intended membership exists or that another DENY changes the result. Protect all identifiers with QUOTENAME and keep the permission name sourced from the catalog. That catalog column uses a different collation from principal names, so the generator adds COLLATE DATABASE_DEFAULT before joining the strings. The review should identify the user and object clearly enough to inspect every proposed change.

DECLARE @RoleName sysname=N'ReviewedReaderRole';
SELECT u.name AS UserName,
 N'ALTER ROLE '+QUOTENAME(@RoleName)+N' ADD MEMBER '+QUOTENAME(u.name)+N';' AS MembershipStatement,
 N'GRANT '+p.permission_name COLLATE DATABASE_DEFAULT+N' ON OBJECT::'+QUOTENAME(OBJECT_SCHEMA_NAME(p.major_id))+N'.'
 +QUOTENAME(OBJECT_NAME(p.major_id))+N' TO '+QUOTENAME(@RoleName)+N';' AS RoleGrantStatement,
 N'REVOKE '+p.permission_name COLLATE DATABASE_DEFAULT+N' ON OBJECT::'+QUOTENAME(OBJECT_SCHEMA_NAME(p.major_id))+N'.'
 +QUOTENAME(OBJECT_NAME(p.major_id))+N' FROM '+QUOTENAME(u.name)+N';' AS DirectRevokeStatement
FROM sys.database_permissions AS p
JOIN sys.database_principals AS u ON u.principal_id=p.grantee_principal_id
WHERE u.type IN('S','U','G','E','X') AND u.principal_id>4
AND p.class=1 AND p.minor_id=0 AND p.state='G';

Preserve DENY and Delegation Semantics

A DENY is not a GRANT waiting for a new destination. Moving it into a role can affect every member, while removing it can expose rights inherited elsewhere. Review those entries under their intended restriction policy. Column-level permission precedence also has documented nuances that require actual tests.

GRANT_WITH_GRANT_OPTION permits delegation. Replacing it with a plain role grant changes that capability, and giving a role delegation rights can broaden who can grant access. Keep grantor relationships and any dependent permission changes in the review.

Schema and database-level permissions have broader scope than one object. A narrow object-generator should not silently expand them into many current objects or shrink their future-object behavior. Treat each class according to its actual scope. The inventory exposes those differences so the change can preserve them deliberately. Simplification succeeds only when the security meaning is retained, not merely when fewer rows remain in the catalog.

Review Public and Server Scope Separately

Every database user participates in public's permissions. Those entries belong to a separate review because changing them affects a broad population. The next query exposes public's explicit database permissions rather than mixing them with direct user candidates. Expect many SELECT rows with negative major_id values; those are the built-in grants on system objects.

Server-level permissions live in sys.server_permissions and server role memberships. This article's database inventory does not include that wider scope. Repeat an appropriately scoped review at server level when the access requirement includes it.

Keep contained users and external identities in their supported authentication context. A database-role change does not create or repair the server identity mapping. Likewise, removing a direct permission from one database does not settle access to another database. State the scope of the review in the change record so a clean result is not presented as an instance-wide permission certification.

SELECT state_desc,permission_name,class_desc,major_id,minor_id
FROM sys.database_permissions
WHERE grantee_principal_id=DATABASE_PRINCIPAL_ID(N'public');
SELECT s.name,p.state_desc,p.permission_name,p.class_desc
FROM sys.server_permissions AS p
JOIN sys.server_principals AS s ON s.principal_id=p.grantee_principal_id
WHERE s.type IN('S','U','G') AND s.principal_id>4;

Verify Access Before Removing Rights Granted Directly to Users

Test permitted and refused actions under representative users after role membership and grants are established. Use supported impersonation and effective-permission functions, then restore the administrative context. Validate the application's actual operations as well as metadata.

Only then remove the reviewed redundant direct grants and rerun the tests. Preserve the original permission evidence through the rollback window. Unexpected access changes need investigation rather than another broad grant that hides the cause. Keep one reviewed mapping from each old direct grant to its replacement role permission and membership.

Check users belonging to multiple roles, too. Their combined permissions can expose broader access than a test performed with only the new role membership.

Permissions granted directly to users become manageable when the inventory feeds a deliberate role design. Keep state, scope, delegation, and public rights visible. Generate reviewable candidates, test effective access, and remove only the entries whose intended behavior has been safely preserved through the role.

Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and Auditing WITH GRANT OPTION: Who Can Hand Out Permissions.

A shorter permission list is not a safer design, it is useful only when the intended access remains correct.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.