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;

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply