Trigger for DML

Question

Trigger for DML

Tara-1044200

SSCoach

Points: 15951
More actions
September 14, 2010 at 3:07 pm

#229292

How can i create a trigger on database so that it prohibits DML(insert,update,delete) acitivuty for few user1 and user2 on table1 and table2?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

How about you just deny insert/update/delete (or don't grant to begin with) permission on those tables to those users? Triggers should be a last resort.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

I am doing the same right now but i would like to automate the process so that every database newly created gets this effected and the imp thing is the tables which has to be prohibited are create later so if i have a trigger that can prohibit users to change anything after creation.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

How are you automating the creation of a trigger?

Couldn't you just use the same method to automate the deny of insert/update/delete?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I want to do that in the model database so that it would take effect into all databases created.

I can do that same DENY on model because tables are created after the database.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

is there a way i cna create a trigger in model database so that it fires when ever a table is create with a name like "secure%" and the functionality of the trigger should be to DENY INSERT,UPDATE,DELETE on those tables.

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 6

you should be able to do this, but deny to who? Everyone? If that's the case, then why not just prevent the table from being created?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

I thought i have mentioned that before, anyways it has to deny for every one except 2 windows groups.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 8

I dont think we can create trigger ON DATABASE for DML acitivities by using event class.

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

Tara-1044200 (9/14/2010)
I dont think we can create trigger ON DATABASE for DML acitivities by using event class.

No you can't - DML triggers are at the table level, which is why I was curious how you were going to automate this.

You're better off setting up granular permissions, and when the table is created, just either explicitly deny those users, or don't grant it to them in the first place (via the db_datawriter, db_owner, serveradmin, sysadmin roles, etcv).