A user granted SELECT WITH GRANT OPTION can grant that permission to someone else. Years later, the delegation can remain after the original project ends. Audit the database and server permission catalogs, trace who granted access to whom, and remove delegation without accidentally removing necessary base access.
Understand the Extra Power
A normal GRANT lets a principal use a permission. WITH GRANT OPTION adds the ability to pass that permission to other principals. This is narrower than sysadmin but still powerful, especially on sensitive objects or broad server permissions. A grant chain can outlive the person or project that created it.
I treat delegated access as an ownership question. Who is supposed to approve new readers of this data today? If the answer is a central access process, an old delegation on a personal login deserves review. Do not revoke it before mapping downstream grants that depend on it.
Find Grant Option Rows in the Database
In sys.database_permissions, state = 'W' identifies a GRANT_WITH_GRANT_OPTION row. Join grantee and grantor principal IDs to names, and use class and major_id to locate the securable. A database-level permission has different identifier meaning from an object-level permission, so include the class description.
SELECT p.class_desc, p.permission_name,
p.major_id,
OBJECT_SCHEMA_NAME(p.major_id) AS object_schema,
OBJECT_NAME(p.major_id) AS object_name,
grantee.name AS grantee_name,
grantor.name AS grantor_name,
p.state_desc
FROM sys.database_permissions AS p
JOIN sys.database_principals AS grantee
ON grantee.principal_id = p.grantee_principal_id
JOIN sys.database_principals AS grantor
ON grantor.principal_id = p.grantor_principal_id
WHERE p.state = 'W'
ORDER BY p.class_desc, p.permission_name, grantee.name;Run this in each database in scope. Metadata visibility and implicit role permissions can limit what the query shows; it lists explicit delegation rows, not every effective permission. A database owner or high-privilege role has powers that this one filter does not describe.
Check the Grant Option at Server Scope
sys.server_permissions uses the same state W for delegated server permissions. Join to sys.server_principals for names. Review high-impact permissions first, then match each one to an approved access request. A server-level grant cannot be inferred from one database's catalog.
SELECT p.class_desc, p.permission_name,
grantee.name AS grantee_name,
grantor.name AS grantor_name,
p.state_desc
FROM sys.server_permissions AS p
JOIN sys.server_principals AS grantee
ON grantee.principal_id = p.grantee_principal_id
JOIN sys.server_principals AS grantor
ON grantor.principal_id = p.grantor_principal_id
WHERE p.state = 'W'
ORDER BY p.permission_name, grantee.name;A row for a Windows group can be more consequential than one for a single SQL login because its membership can change outside SQL Server. Check group ownership and membership through the approved identity source. The database catalog does not show that external membership history.
Trace Who Granted the Next Link
Catalog rows include grantor_principal_id and grantee_principal_id. For a given permission and securable, find rows where the grantor is the grantee of a W row. Continue until no downstream grants remain. This is a chain of recorded explicit grants, not proof of every effective access path through roles or ownership.
SELECT parent.permission_name,
source.name AS original_grantee,
child.name AS downstream_grantee,
nextgrant.state_desc AS downstream_state
FROM sys.database_permissions AS parent
JOIN sys.database_permissions AS nextgrant
ON nextgrant.grantor_principal_id = parent.grantee_principal_id
AND nextgrant.class = parent.class
AND nextgrant.major_id = parent.major_id
AND nextgrant.permission_name = parent.permission_name
JOIN sys.database_principals AS source
ON source.principal_id = parent.grantee_principal_id
JOIN sys.database_principals AS child
ON child.principal_id = nextgrant.grantee_principal_id
WHERE parent.state = 'W';Extend this traversal recursively for a longer chain, with a cycle guard and a bounded depth. Also consider column-level minor_id when auditing column permissions. I export the chain before any REVOKE so the owner can approve which dependent grants should remain through a new authorized grantor.
Revoke the Grant Option, Keep the Base Permission
REVOKE GRANT OPTION FOR removes the right to pass a permission onward while retaining the base permission for the grantee. If that principal granted the permission to others, SQL Server requires CASCADE. CASCADE affects dependent grants, so understand and plan their replacement before using it.
-- Review dependent grants and test in a restored copy first.
REVOKE GRANT OPTION FOR SELECT
ON OBJECT::dbo.CustomerLedger
FROM [ReportDelegator]
CASCADE;Without CASCADE, the revoke fails with error 4611 when downstream grants depend on the delegation. Do not treat that error as a nuisance to bypass. It is warning you that other principals' access can change. The exact syntax and securable class must match the permission row being remediated. Verify the grantee still has SELECT if that is the intended outcome, and check every downstream principal afterward.
Rebuild an Approved Access Path
If downstream users still need access, grant it through the proper role or approved owner before removing the old delegation, following the organization's change process. Test application connections under their real identities. Record before and after catalog rows, not only the REVOKE success message. A role membership or another grant can mask the effect of a revoked chain, so test effective access explicitly.
I review new W rows periodically and require an owner and expiration review date. A delegated permission can be legitimate for a managed service, but it should be visible. The audit succeeds when each delegation has a current reason and the chain is understandable without tracing an old ticket archive.
Include Class and Minor ID in a Real Chain
The simple two-level query compares permission name and major ID, which is enough to illustrate the grantor relationship. A production audit also compares class and minor_id, distinguishes object from schema or database scope, and follows chains recursively. Column-level grants can have a nonzero minor_id; omitting it can join grants that apply to different columns. Record the full securable and principal IDs in the evidence file.
Role membership and ownership can grant effective access without a W row. Likewise, an account can receive the same permission through two paths. Revoking one delegated chain does not necessarily remove effective access. Test with the actual principal or a safe impersonation method where authorized, and document each remaining path.
Plan a CASCADE Change
Before using CASCADE, list all grants made by the delegator and ask the data owner which ones still belong. Reissue approved access through a managed role or an authorized grantor. Rehearse the REVOKE on a restored copy or a small test securable and inspect catalog rows before and after. A CASCADE action can be broader than the one line of SQL suggests when delegation has passed through several accounts.
I schedule the change when the affected applications can be tested. A successful statement proves only that SQL Server applied it. Confirm that the original grantee retained the base permission, that obsolete downstream grants disappeared, and that approved users still can perform their work. Save the before map for rollback planning.
Keep Delegation Rare
Delegated granting is a useful mechanism when a trusted service must manage access, but it should have a clear owner and regular review. For ordinary reporting users, direct role membership is easier to audit than a chain of personal grants. Keep the grantor name and approval case beside the catalog inventory. If an account leaves, its delegated grants should be reviewed as part of departure handling.
Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and Deny Drop Permission for a Table.
WITH GRANT OPTION is not a harmless extra permission, it is authority to pass access onward.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
