Lance Harra Blog

Denying permissions on a column.

I was recently asked to block or deny access to 2 columns in our reporting database.  I noticed that you can only deny 1 column permission at a time.

This command will deny select on the table and update on the columns.

DENY Select,Update (column_a, column_b) ON my_table TO security_account

If you run the commands as 2 separate statements you get column level deny for select and update

DENY Select (column_a, column_b) ON my_table TO security_account

DENY Update (column_a, column_b) ON my_table TO security_account

Legacy Comments


AjarnMark
2003-10-14
re: Denying permissions on a column.
Lance, good catch! I wouldn't have thought of it. But I think you can do this if you'll specify the column list for every permission, such as:
DENY Select (column_a, column_b), Update (column_a, column_b) ON my_table TO security_account