deny insert / update / delete on a handful of tables

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.

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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics