The Microsoft documentation for sys.database_permissions mentions that the valid values for state are "D", "R", "G", "W". The corresponding values for state_desc are "DENY", "REVOKE", "GRANT", "GRANT_WITH_GRANT_OPTIONS".

The value of "R" / "REVOKE" is a surprise to me. Isn't "REVOKE" just part of the syntax for eliminating a previously GRANT'ed or DENY'ed permission?

Is it possible for the values or "R" / "REVOKE" to be present in this system view? If so, under what circumstances would it appear?

I know that if you REVOKE a DENY'd permission, the DENY'd permission goes away; and if you REVOKE a GRANTed permission the GRANTed permission gets removed. Under what circumstance would SQL Server register the REVOKE'ing in sys.database_permissions instead of simply removing the existing GRANT/REVOKE/GRANT_WITH_GRANT_OPTIONS row?

  • The system does not support cross-site duplicates (for better or worse). This Q & A is on-topic and has value here, so we'll keep it as it is. Thanks!
    – Paul White
    May 19 at 10:22

REVOKE doesn't just remove access, rather it undoes a specific permission that was either explicitly given by a GRANT or DENY. In other words, if a permission is explicitly denied on an object for a security principal (e.g. User, Login, etc), that explicitly denied permission can be removed by using REVOKE on it.

After some research it looks like the use-case for "R" as a potential value of the state column in the sys.database_permissions view is for column level permissions that contradict the parent table's or view's permissions as per this Stack Overflow answer by Mark Chesney:

For objects which can have column permissions, such as tables or views, the presence of DENY or GRANT object permissions requires REVOKE for column permissions to be persisted.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.