Understanding GRANT, DENY, and REVOKE in SQL Server

By:   |   Comments (11)   |   Related: > Security


I'm having a bit of trouble understanding how GRANT, DENY, and REVOKE work together in SQL Server, especially when you consider things like schemas. Which setting trumps the others? How do I undo security?


The first thing to understand about SQL Server security is that SQL Server is built with the idea that security has to be granted. In other words, if SQL Server sees no applicable permissions for a user, the user has no access. If you're familiar with Windows file or share permissions, it works the same way.


In order for a user to be able to do something, he or she must be given permission to do it. We do this via the GRANT command. However, before we demonstrate that, let's do some setup of a test role and a test user in a test database I've created (aptly called TestDB):

Create Test User

EXEC sp_addrolemember @rolename = 'TestRole', @membername = 'TestUser';

Create Tables and Permissions

Now let's create a schema, a couple of tables, and let's GRANT the ability to select against the first table.

CREATE TABLE Test.TestTable (TableID int);
CREATE TABLE Test.TestTable2 (TableID int);

Test Harness Queries

Once that is done, let's use two "test harnesses" to test the user's ability to access the tables in question. Note that with the current permissions, the user should only be able to issue a SELECT against the first table.

-- Test Harness to verify how permissions work for Test.TestTable.
SELECT * FROM Test.TestTable;
-- Test Harness to verify how permissions work for Test.TestTable2.
-- This should fail initially, as there is no permission for this table
SELECT * FROM Test.TestTable2;

Seeing the Permissions

In order to see the permissions that are granted, we'll use the sys.database_permissions catalog view. If you issue this query now, you'll see the first GRANT we made. Re-use this query to see the permissions after each change:

-- Query sys.database_permissions to see applicable permissions
SELECT dp.class_desc, s.name AS 'Schema', o.name AS 'Object', dp.permission_name, 
       dp.state_desc, prin.[name] AS 'User'
FROM sys.database_permissions dp
  JOIN sys.database_principals prin
    ON dp.grantee_principal_id = prin.principal_id
  JOIN sys.objects o
    ON dp.major_id = o.object_id
  JOIN sys.schemas s
    ON o.schema_id = s.schema_id
WHERE LEFT(o.name, 9) = 'TestTable'
  AND dp.class_desc = 'OBJECT_OR_COLUMN'
SELECT dp.class_desc, s.name AS 'Schema', '-----' AS 'Object', dp.permission_name, 
       dp.state_desc, prin.[name] AS 'User'
FROM sys.database_permissions dp
  JOIN sys.database_principals prin
    ON dp.grantee_principal_id = prin.principal_id
  JOIN sys.schemas s
    ON dp.major_id = s.schema_id
WHERE dp.class_desc = 'SCHEMA';


REVOKE undoes a permission, whether it's a GRANT or a DENY (more on DENY in a minute). If you issue the following REVOKE and then check the permissions, you'll note that the GRANT that was previously present for Test.Table1. After issuing the revoke command, re-run the test harness queries above against that table and you'll see that the user cannot query the table any longer.

-- Let's undo the permission using REVOKE;

Remember, REVOKE doesn't cancel a GRANT. It doesn't block a GRANT. It removes a permission at the level specified to the security principal (user or role) specified. That's why we say it undoes a permission.


DENY blocks access. DENY trumps all other access. If a user has both a GRANT and a DENY on a given object, by whatever means, the DENY will take effect. For instance, let's consider the case of a GRANT SELECT against the Test schema. This would give the ability to issue a SELECT against any table or view in the Test schema. Try just applying this permission, re-checking the permission, and then testing the user's access to both Test.TestTable and Test.TestTable2. You'll see the user can now issue a SELECT query against both tables.

If you're not familiar with schemas and how they affect permissions, see this tip on nested permissions due to securables. Sometimes, if you look for an explicit permissions against a table or stored procedure, you won't see it. However, the user can execute the SELECT or EXECUTE respectively. If this is the case, then the permission is on a securable that contains the object. That's what we're doing here. The Test schema contains the TestTable and TestTable2 tables. So if a user has SELECT permissions against the Test schema, it also has SELECT permission against any tables and views within the Test schema.

-- Permission at the schema level

Now let's apply a DENY. In this case I'm applying a DENY explicitly to the test user instead of going through a role. And I'm only applying it to the Test.TestTable object. Now re-run the test harness queries. You'll see the access is denied. However, you can still query Test.TestTable2. There isn't a DENY applied against it.

-- Specific DENY will block the GRANT 
DENY SELECT ON OBJECT::Test.TestTable TO TestUser;

And if you re-run the permissions script, you'll see all the permissions granted, to include the DENY.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 24, 2020 - 12:16:18 PM - Joe Celko Back To Top (87835)
As an aside, the SQL-86 standard would let you GRANT privileges but had no way to REVOKE them. This was the first thing to come out of the old ANSI X3 H2 standards committee, and we wanted to get something and print as fast as possible. The DENY didn't come until later and wasn't part of the standards. The standards actually have a good bit more to say on what you can grant to a schema object, but that's a topic for a whole article by itself. I've always felt we don't spend enough time teaching the DCL (data control language) when we teach SQL.

Tuesday, March 17, 2020 - 5:36:00 PM - DL Back To Top (85132)

I have tried several times, but the following does not work on memebers of the AD group or database role:

DENY CREATE SCHEMA TO [an AD group or a database role];

Any idea why, and how to make it work?

Monday, August 10, 2015 - 3:25:02 PM - K. Brian Kelley Back To Top (38414)

Correct, the DENY would block access.

Revoking the DENY would re-establish access.

If you were to issue the REVOKE and there was no permission at the user level, then nothing would happen. There will be no error. SQL Server will tell you the command completed successfully. Since the REVOKE is for the user, and the role permission is unchanged, the user would still have access.

Monday, August 10, 2015 - 12:51:55 PM - Jared Hardman Back To Top (38413)

studying for 70-462 and came across this post.  let me see if I have this right:

GRANT or DENY add an explicit access rule where it is assumed that if no rule applies, there is no access, and a DENY trumps any other explicit permissions.  On the other hand, REVOKE merely removes a previously existing rule.

Therefore, if userA was part of a role that had explicit access GRANTED to select from tableA, but he was DENIED "select" access at the user level, the DENY would ensure he couldn't "select" from the table.  That's right, so far I think.

Now I could remove the DENY with a REVOKE: REVOKE SELECT ON Object::tableA FROM UserA.  This would then allow userA to SELECT from the table since the rule that is left after the REVOKE is a GRANT at the role-level.  right?

Now if my userA was SELECTing from the table based on access at the role-level, no explicit permission was either GRANTed or DENYed to that specific user, and I ran the same statement, it would have no effect, because there would be no rule at that level to revoke, right?  would that throw an error?

Thursday, March 26, 2015 - 10:43:14 AM - S.E. Back To Top (36717)

Hi, in the REVOKE section, some words are missing (with bold below):


... If you issue the following REVOKE and then check the permissions, you'll note that the GRANT that was previously present for Test.Table1 is now gone. After issuing the revoke command, re-run the test harness queries above against that table and you'll see that the user cannot query the table any longer.

Friday, April 19, 2013 - 4:17:39 PM - Dimitri Shvorob Back To Top (23450)


"REVOKE undoes a permission, whether it's a GRANT or a DENY"

"REVOKE doesn't cancel a GRANT" 

It seems like REVOKE does cancel a GRANT after all?

Monday, March 11, 2013 - 9:27:37 AM - Cary Davis Back To Top (22704)

Noice!  Great article.  Clearly and simply explained.  Thank you.

Wednesday, February 27, 2013 - 5:50:32 PM - Joe Celko Back To Top (22453)

I have never seen a good discussion on DCL in any SQL book; including my own books!  Technically DCL is access control, not security, so when you add security and encryption, you have a  good book. Nice job! 


Wednesday, February 27, 2013 - 2:11:21 PM - K. Brian Kelley Back To Top (22450)

Hi Tim, exactly. I've got another tip coming that covers that.

Wednesday, February 27, 2013 - 12:41:11 PM - TimothyAWiseman Back To Top (22448)

Fantastic article, thank you for providing it.

It is worth noting that SQL Server looks first to see if a user is in the SA or database owner role for the database in question.  If they are, SQL Server looks no further and will not prevent someone from executing the command even if there is an explicit deny in place.

Wednesday, February 27, 2013 - 11:14:45 AM - Bill Back To Top (22446)

Good article, nice examples, explanations and test scripts.

get free sql tips
agree to terms