SQL Server Permissions – Grant Deny Permission Stacking

permissionsrolesql server

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.

Best Answer

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.

Related Solutions

MySQL Grant Permission

'insert' permission should be all you need to do an insert:

create database stack;
create user dummy;
create table stack.t(v integer primary key);
grant insert on stack.* to dummy;

as dummy:

insert into stack.t values(1);
Query OK, 1 row affected (0.00 sec)

select * from stack.t;
ERROR 1142 (42000): SELECT command denied to user 'dummy'@'localhost' for table 't'

and the grant like this will even apply to tables created after the grant was made - it gives a database privilege

As you also want to select you will also need to grantselecton stack.* to dummy

SQL Server – How to Deny Drop Permission for a Table

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;

http://msdn.microsoft.com/en-us/library/ms173724.aspx

Best Answer

Related Solutions

MySQL Grant Permission

SQL Server – How to Deny Drop Permission for a Table

Related Question