Can sys.database_permissions contain REVOKE? The Microsoft documentation for sys.database_permissions mentions that the valid values for state are &quot;D&quot;, &quot;R&quot;, &quot;G&quot;, &quot;W&quot;. The corresponding values for state_desc are &quot;DENY&quot;, &quot;REVOKE&quot;, &quot;GRANT&quot;, &quot;GRANT_WITH_GRANT_OPTIONS&quot;.
The value of &quot;R&quot; / &quot;REVOKE&quot; is a surprise to me. Isn't &quot;REVOKE&quot; just part of the syntax for eliminating a previously GRANT'ed or DENY'ed permission?
Is it possible for the values or &quot;R&quot; / &quot;REVOKE&quot; 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?
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 &quot;R&quot; 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.