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.

Answer 3

Yes it is possible that sys.database_permissions tables contains the row with state R. R means revoke & it will be there in state column of the table.

We can also have D(Deny), G(Grant), W(Grant with Grant option) along with R.

The datatype of this state column will be char(1)

see the below link for the better understanding.

http://msdn.microsoft.com/en-us/library/ms188367.aspx