Skip to content

Instantly share code, notes, and snippets.

@mches
Created May 20, 2015 19:33
Show Gist options
  • Save mches/d2282946fbe7f50a708b to your computer and use it in GitHub Desktop.
Save mches/d2282946fbe7f50a708b to your computer and use it in GitHub Desktop.
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;
@mches
Copy link
Author

mches commented May 20, 2015

@mches
Copy link
Author

mches commented May 20, 2015

The results of the SELECT statement on SQL Server 2008:

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment