Protecting tables with triggers
-
I want to use a trigger to stop users from eliminating a table I am able to do this using the following trigger for a delete statement:
create trigger Master_Protect
on test_master
for delete as
rollback
Print 'Business rules do not allow for table deletion'
I am wondering how I do the same thing to prevent someone dropping and/or truncating a table?
-
For truncate and drop you would have to be in the dbo role or sa role. Just take everyone out of that and grant them the correct permissions on the table which would include the omission of delete permissions on the one table.
Tom
-
Unfortunately this is not an option for our group (bureaucracy of a large organization) I am more or less trying to protect ourselves from ourselves with this measure.
I take it there is no way to do this with triggers?
-
DDL is not affected by triggers.
Tom
-
SQL 2005 has DDL triggers but not an option i guess
-
Thanks guys
-
I hope that table has Primary Key.
If no, create it.
Than create dummy table referencing this table with FK. Truncate will not work then.
_____________
Code for TallyGenerator -
That did the trick Sergiy -- Thanks
It appears I cannot delete the table unless I remove the constraint. Nice!
-
Great idea Sergiy.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply