9

For the role db_denycustomer, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:

DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer

...and it works fine. Cool! But, why?

What I've read in related articles is that permissions stack, but DENY takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY hides the code column too.

Could you please elaborate on this?

I have also included the default db_datawriter and db_datareader roles to the user that I tested with.

10

This is documented behavior provided for backwards compatibility. Documentation excerpt:

Caution 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.