When would sys.database_permissions contain a row with state = 'R'?

Question

The MSDN documentation for sys.database_permissions says that the state column can be either 'G', 'D', 'R', or 'W'. The 'R' value has a description of 'REVOKE', which makes it sound like a row with this value would correspond to a revoked permission. However, as best I can tell from the docs for REVOKE, revoking a permission completely removes it, so I would expect that to just remove the row from sys.database_permissions. That's what happened when when I tested granting and then revoking a permission; after GRANT the permission shows up in that view, and after REVOKE it disappears.

My question: under what circumstances would this view ever contain rows with state set to 'R'? I'm asking this because I'm not sure whether I need to handle 'R' rows when examining this view in code.

I can think of a few potential scenarios where this might occur, but haven't found any confirmation:

• 'R' rows might appear if you granted some blanket permission and then revoked a more granular permission which was implied by the blanket permission (the granular permission would show up as 'R'). So far I haven't found any such permissions.
• 'R' rows might appear very briefly while SQL is processing a REVOKE command and then the entire row disappears. I haven't observed this, but presumably there's only a very small window of time when it would appear.

Answer 1

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. Below is a working example, tested on SQL Server 2008, that demonstrates when a record with state R can exist in sys.database_permissions. If the order of the GRANT and REVOKE statements is reversed, then the record with state R is not persisted.

https://gist.github.com/mches/d2282946fbe7f50a708b

CREATE USER RevokeTestUser WITHOUT LOGIN;

REVOKE CONNECT TO RevokeTestUser AS dbo;

CREATE TABLE dbo.RevokeTest (
     col int NOT NULL
);

GRANT SELECT ON dbo.RevokeTest TO RevokeTestUser AS dbo;
REVOKE SELECT ON dbo.RevokeTest (col) TO RevokeTestUser AS dbo;

SELECT *
FROM sys.database_permissions
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID(N'RevokeTestUser');

DROP USER RevokeTestUser;

DROP TABLE dbo.RevokeTest;

These are the results of the SELECT statement:

class   class_desc         major_id     minor_id    grantee_principal_id   grantor_principal_id   type   permission_name   state   state_desc
1       OBJECT_OR_COLUMN   1081939822   0           31                     1                      SL     SELECT            G       GRANT
1       OBJECT_OR_COLUMN   1081939822   1           31                     1                      SL     SELECT            R       REVOKE

Answer 2

As answered by Jack Richins on Twitter (thanks to @Remus Rusanu for forwarding):

I believe it occurs wi column level permissions that contradict table or view perms.

I tested this and he is correct.