Can sys.database_permissions contain REVOKE?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply