Deny select on table Grant select on columns

There is a little warning in books-on-line about the interaction of Deny and Grant (http://msdn.microsoft.com/en-us/library/ms173724.aspx).  “A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility.”

However, I found a little caveat to this point.  The order in which you issue the GRANT and the DENY affects this behavior.  If you call DENY first (deny on the table) then GRANT, you get permission to select the columns but not the table.  If you do the DENY second, the you do not get permission on the columns. Try it out.

use AdventureWorks2012
go
deny select on Person.Person to testUser;
grant select on Person.Person (FirstName, LastName) to testUser;

execute as user= 'testUser';

select * from Person.Person;
select FirstName, LastName from Person.Person;

revert;

grant select on Person.Person (FirstName, LastName) to testUser;
deny select on Person.Person to testUser;

execute as user= 'testUser';

select * from Person.Person;
select FirstName, LastName from Person.Person;

revert;

Leave a Reply

%d