Prevent Truncates via Trigger

Question

Prevent Truncates via Trigger

street9009

SSC Veteran

Points: 232
More actions
July 6, 2010 at 7:52 pm

#143988

Is it possible to create a trigger to stop TRUNCATE statements in their tracks? I'd like to put this on every database on a SQL server I manage. There is a trigger there now for DELETE (INSTEAD OF DELETE) but trying to modify for TRUNCATE fails.
Any help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Truncate doesn’t activate delete trigger nor instead of delete trigger. Also unlike delete statement regular users don’t have permissions to truncate a table and can not be granted those permissions. Only users that can alter the table can run a truncate table on this table. If your users can truncate the table, it means that they got to many permission to begin with (most chances they are member of db_owner role, or worse – sysadmin role). In that case you should make sure that they have just the permissions that they need and they won’t be able to truncate the table.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

street9009 SSC Veteran Points: 232 More actions · Answer 2

So there's no way to prevent all users from truncating a table without re-setting all permissions?

Lowell SSC Guru Points: 323506 More actions · Answer 3

"resetting" their permissions is not a big deal...fairly easyactually, but it's seems iuntimidating if you have not done it before.

say you need a role where they can read/write and also execute procedures...but should not have dbowner rights as previously explained, where they can truncate(and DROP DATABASE as well!)

script the new role:

CREATE ROLE [MyReadWriteExecuteRole]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [MyReadWriteExecuteRole]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [MyReadWriteExecuteRole]

GRANT EXECUTE TO [MyReadWriteExecuteRole]

from there, add a user, add that user to the role and test it a bit. after you are confident it is working like you want,

I'd do the next step in the SSMS GUI:

go to a user in the database, and simply remove the other roles it belongs to, and add this one.

maybe move only one or two other real users to test it gradually, then do it to everyone a week or two later.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!