Friday, May 28, 2010

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]

5 comments:

Unknown said...

Hi

Thanks For this great Info.

Can u also please help me to grant user correct permission.

DB name : GetStarted

Sql:

"
USE GetStarted;
GRANT CREATE TABLE,CREATE VIEW,EXECUTE, ALTER,Select,Insert, Update TO LetmestartU;
GO
"


Now the problem is that when i log in as "LetMeStartU"
I m unable to create new table from Micorsoft management studio (Right click - > New Table)
IT says that i need to be dbowner to create new one.

But i m able to run query to create table .

So whats i m missing ?

I want that user is able to create table , view , procedure ,alter , update,select through MMS

Thanks

Dinesh Priyankara said...

Hi,

Thanks for the comment.

If you have given the above set of permission, the user LetMeStartU will be able to create tables. Only thing is, user will see warnings when try to create tables from Management Studio but tables can be created and saved. I tested this, it works.

But user will not be able to open it again and alter it unless you have given VIEW DEFINITION permision to him. Once it is given, altering tables is possible too.

Unknown said...

Simple and correct! Thanks

Vinay kumar said...

Greate One.
Lots of Thx...
greate explanation.

Shiv said...

Just to clarify, there is another facet to the description of grant-revoke-deny that you may have missed out.

GRANT and DENY are used to assign explicit permissions. REVOKE is to remove the explicit granted or denied permission. In other words, GRANT or DENY is to insert a row to some table (that is used to look up permissions) and REVOKE is to delete the associated GRANT or DENY row.

So generally we do not use revoke in place of deny though sometimes it might seem interchangeable.