SQL-Saturday #116 – Question of the day: When does a GRANT overrides a DENY in SQL Server?

 

SQLSaturday #116 – Bangalore

WoW!! What a great day it was to learn and connect with SQL guru’s and like minded folks who carry same passion for SQL server as I do. It’s quite an honor to meet and listen @blakhani, @pinaldave, @banerjeeamit, @vinodk_sql, @kashyapa , Rick @Joes2Pros……You guys are truly inspirational

Fellow attendees -  For those of you on Twitter, follow #sqlpass and make sure to check out the #sqlsat116 and #sqlsaturday hashtags to stay up to date

As tweeted by @vinodk_sql “Race to First 10 blog post on #SQLSat116. If you attended the event, we want to hear from you. Drop us a nudge …”

Here’s the first one to start with –

Fellow attendees – During the session on Security Pitfalls, Vinod gave us this interesting question to reply/tweet later.

Question: When GRANT overrides DENY in #sql server? Was asked during a session today in #sqlsaturday #sqlsat116

Answer: An explicit GRANT on a TABLE::COLUMN takes precedence on DENY TABLE #sqlsat116 #sqlsaturday

And here’s a simple test I did to get me answer

We needs 3 users/login (one being S.A.) 

–Here:

–UserOne is SA on SQL Instance

–UserTwo has got READ/WRITE privileges on TEST database

–UserThree is DBO (database_owner) for TESTDB

–Step1. Create a sample table say ‘iden_test’ with 2 cols (id,name)

USE [test]

GO

CREATE TABLE [dbo].[iden_test](

     [id] [int] IDENTITY(1,1) NOT NULL,

     [name] [varchar](10) NULL

)

–Step2. Run below T-SQL to DENY READ (SELECT) to UserTwo

DENY SELECT on OBJECT::dbo.iden_test TO UserTwo

GO

–Step 3. Try running below query and this should fail with permissions issues

SELECT id FROM dbo.iden_test

Go

Error: Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object ‘iden_test’, database ‘test’, schema ‘dbo’.

–Step 4. Login to server with UserThree (SA user) and run below T-SQL to GRANT

GRANT SELECT ON OBJECT::dbo.iden_test(id) TO UserTwo

Go

 –Step 5. Login again as UserTwo and run the SELECT (per Step 3) again

SELECT id FROM dbo.iden_test

Go

I can get the results for the specific column now.

Conclusion – A Grant on Table (Column) overrides the DENY on same object.

Varun as iVarund