Granting Column Wise Permission

You might know that you have the option of allocating permission Column wise. For example, you can GRANT permission to another user so that he can select Name column of the Employee table while does not have permission to select Salary column.

Let us assume with following case.

I have a table ,( let us say , Production.ProductCategory)  I grant select permission for two columns while deny permission for the entire table for a user as shown below.

So the question is , what is the effective rights. Can he select those columns or will he be denied on select permission?

What is the rule for security,

Denial of access always out weights a grant of access, with the exception of the sysadmin role at server.

If you go by the above rule, the user should not be able to access those two columns. But to your surprise, by default user can select those columns.

So what is the concept behind this?

MSDN says,

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.

Can you change this.

Yes you can by running following script.

However, you need to restart SQL Server instance after running the above query.