Grant > Deny?

Question

Grant > Deny?

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
May 21, 2016 at 11:53 am

#77480

Comments posted to this topic are about the item Grant > Deny?
May 22, 2016 at 11:54 pm

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Tom Nicol SSCrazy Points: 2252 More actions · Answer 1

Good question Steve, thanks.

Note that this only works if Common Criteria compliance is NOT enabled. One of the things that the Common Criteria option does is to make a table level DENY overcome a column level GRANT.

sestell1 SSChampion Points: 10230 More actions · Answer 2

sestell1

SSChampion

Points: 10230

May 23, 2016 at 7:30 am

#1880765

Well, I learned something today. :angry: :hehe:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

sestell1 (5/23/2016)
Well, I learned something today. :angry: :hehe:

Me too!

Revenant SSC-Forever Points: 42467 More actions · Answer 4

I got it right, but it was a head scratcher. Thanks, Steve!

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 5

A useful question with a really interesting script and a very good explanation. Thanks, Steve. 🙂 A more detailed description can be read in

https://www.mssqltips.com/sqlservertip/2894/understanding-grant-deny-and-revoke-in-sql-server/

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

sestell1 (5/23/2016)
Well, I learned something today. :angry: :hehe:

Me too.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Nice question, but the reference chosen (https://msdn.microsoft.com/en-us/library/ms188371.aspx, the "GRANT Object Permissions (Transact-SQL)" page) was an unfortunate choice, because it still omits the important statement that column level grant overriding table level deny will be removed in a future release while https://msdn.microsoft.com/en-us/library/ms187965.aspx,the "GRANT (Transact-SQL)" page has contained this statement in all versions since 2008 R2.

I don't know why Microsoft is carrying both this "GRANT Object Permissions (Transact-SQL)" page and the more up to date "GRANT (Transact-SQL)" page and has been doing so at least since SQL Server 2008 R2 (both pages have 2008 R2 and 2012 verions as well as the current version). The GRANT page says it is applicable to Azure SQL Data Warehouse and Parallel Data Warehouse; the GRANT Object Permissions page doesn't. Of course both say the apply to SQL Server (2008 onwards) and Azure SQL Server.

Tom

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

The rearrangement of information is intended to make keeping things up to date easier for MS and to prevent stale pages. I'm not sure this works great, but I understand what they are trying to do.

The "removal" notice isn't one I put a lot of stock in. This is one that I could see them not ever removing.

bgodick SSC-Addicted Points: 459 More actions · Answer 9

bgodick

SSC-Addicted

Points: 459

May 25, 2016 at 10:28 am

#1881380

Thanks, learned something new.

webrunner SSC-Dedicated Points: 31863 More actions · Answer 10

Thanks for the question! I learned something yet again. DENY and GRANT are really tricky.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

sektor81 Say Hey Kid Points: 679 More actions · Answer 11

sektor81

Say Hey Kid

Points: 679

June 5, 2016 at 1:35 pm

#1883289

This one was easy 😎