Understanding GRANT, REVOKE and DENY T-SQL Commands

GRANT, REVOKE, and DENY commands are T-SQL commands in SQL Server for managing permission. Although we know the correct usage of them, REVOKE and DENY have confused most of us, what exactly SQL Server does for these two commands. Here is a brief explanation on them;

• GRANT – Let users to perform an operation on objects.
• REVOKE – Removes assigned GRANT permissions on an object for one or more operations. Main thing you have remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object for the operation, user will be able to perform the operation.
• DENY – Denies permission to the object for an operation. Once it set, since it takes precedence over all other GRANT permissions, user will not be able to perform the operation against the object.

Here is a code that shows it clearly.

-- create a login and user

CREATE LOGIN [Joe] WITH PASSWORD = '1', CHECK_POLICY = OFF

GO

USE [AdventureWorks]

GO

CREATE USER [Joe] FOR LOGIN [Joe]

WITH DEFAULT_SCHEMA = [dbo]

GO

-- grant permission on product table

GRANT SELECT ON OBJECT::Production.Product TO [Joe]

GO

-- create a role and set SELECT permission to it

-- and add Joe to the role

CREATE ROLE [NewRole]

GO

GRANT SELECT ON [Production].[Product] TO [NewRole]

GO

EXEC sp_addrolemember N'NewRole', N'Joe'

GO

-- Run SELECT with Joe's credentials and see

-- He sees records

EXECUTE AS USER = 'Joe'

SELECT * FROM Production.Product

REVERT

-- Remove permisson assigned to him

REVOKE SELECT ON OBJECT::Production.Product FROM [Joe]

-- He still sees data, because not all GRANTs are

-- removed by REVOKE

EXECUTE AS USER = 'Joe'

SELECT * FROM Production.Product

REVERT

-- This explicitly denies permission on Product to Joe

-- Once this is eecuted, he will not be able to see data

-- even we grant him again.

DENY SELECT ON OBJECT::Production.Product TO [Joe]