How do I deny insert / update / delete on a handful of tables for all DB users on a database? These users need and have DB_Datawriter permissions.
deny insert / update / delete on a handful of tables
June 30th, 2015 4:02pm
You can use DENY to prevent a user from doing something that another permission or role membership would otherwise allow. But it's rarely done. It's typically better to create your own roles and grant it the permissions the users actually need, and no more.
eg
create user fred without login alter role db_datawriter add member fred alter role db_datareader add member fred create table t(id int primary key, a int) deny insert, update, delete on t to fred insert into t(id,a) values (1,1) execute as user='fred' select * from t delete from t --fails revert
David
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 4:19pm
Duh .. I did a revoke insert, update, delete on tablexyz from rolexyz.
DENY insert, update, delete on tablexyz from rolexyz works. Thanks!
June 30th, 2015 4:36pm