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.
REVOKE doesn’t just remove access, rather it undoes a specific permission that was either explicitly given by a
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 🙂