Restrict Truncation in the tables

Question

Post reply

Restrict Truncation in the tables

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
April 14, 2009 at 2:00 pm

#129309

Can I restrict truncation in the tables even the account has system admin access. ?
I have created database trigger but this is for object level. The only way I am thinking about creating trigger on each table and check for the truncation records.
Any better idea. ?

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

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

Hi,

Yes, you can turn on trace flag 3231 which will turn NO_LOGS and TRUNCATE_ONLY no ops

Thanks,

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

I think you misread the question, Krishna. The OP wants to stop a table from being truncated (accidentally, I presume).

The only way I know for sure to prevent a truncate statement is to have a foreign key constraint that references the table. Of course, if you're trying to prevent an intentional truncate by a member of sysadmin, there really isn't a way.

Greg

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

An other alternative to Gregs proposal:

(Only a referenced table cannot be truncated without first removing the relationship.)

from BOL: "Truncate table"

You cannot use TRUNCATE TABLE on tables that:

- Are referenced by a FOREIGN KEY constraint.

- Participate in an indexed view.

- Are published by using transactional replication or merge replication.

For tables with one or more of these characteristics, use the DELETE statement instead.

sysadmin is God to sqlserver, at login time, this user is switched to a special mode, so many things you can do to/with regular users, you cannot do with a member of then sysadmins sqlserver group.

Further more, only members of db-owner group can truncate a table. (sysadmin is it all)

So I guess the easiest way is to create an dependant table on that table.

why ? because that will use minimal space!

Off course your MyTable will need a primary key or a unique index to facilitate the FK.

create table T_Dummy_Restrict_Trunc_on_MyTable ( keycol not null)

go

alter tabel T_Dummy_Restrict_Trunct_on_MyTable

add constraint fk_Dummy_Restrict_Trunc_on_MyTable

references MyTable ( keycol)

go

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

I have seen a script where someone set up a "Database level trigger" and caught the sql text that was being sent to the server.

He used it to prevent the dropping of any tables, when he saw the text "Drop Table" he simply stopped the trigger and did nothing, so he could return an error message saying you cannot drop. You could do the same with the "Truncate" statement?

Can't find the example, but that might put you on track, I hope,

Hope it helps,

Cheers,

J-F

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

J-F Bergeron (4/15/2009)
I have seen a script where someone set up a "Database level trigger" and caught the sql text that was being sent to the server.
He used it to prevent the dropping of any tables, when he saw the text "Drop Table" he simply stopped the trigger and did nothing, so he could return an error message saying you cannot drop. You could do the same with the "Truncate" statement?
Can't find the example, but that might put you on track, I hope,
Hope it helps,

You are thinking about Database Triggers. Those work on Data Definition Language statements like DROP TABLE. TRUNCATE is a DATA MANIPULATION LANGUAGE statement, and a DATABASE TRIGGER won't catch that.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Lynn Pettis (4/15/2009)
J-F Bergeron (4/15/2009)
I have seen a script where someone set up a "Database level trigger" and caught the sql text that was being sent to the server.
He used it to prevent the dropping of any tables, when he saw the text "Drop Table" he simply stopped the trigger and did nothing, so he could return an error message saying you cannot drop. You could do the same with the "Truncate" statement?
Can't find the example, but that might put you on track, I hope,
Hope it helps,
You are thinking about Database Triggers. Those work on Data Definition Language statements like DROP TABLE. TRUNCATE is a DATA MANIPULATION LANGUAGE statement, and a DATABASE TRIGGER won't catch that.

My bad, I did not know that... I've been building sql scripts for years now, and I think you just taught me the difference between the "DML" and "DDL'... I've always skipped on those acronyms where finally, they are so simple... Heh!

Cheers,

J-F

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 7

Thanks guys,

Looks like there is only way which ALZDBA has mentioned by creating FK constraints.

I googled a lot but did not find anything new. Can we create a trigger on the table to prevent truncation. ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

TRUNCATE TABLE does not fire triggers defined on a table, so no.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 9

we have data warehouse environment here and millions of rows gets inserted into the tables every night. If I use FK constraints then do you think it will affect the performace of the database. ?

The reason why I am trying to prevent truncation in the table because we have a third party application running which truncates the data if it incremental load. if it is full then it drops the table.

Last week some profile ran accidently and alter the table and our load failed. I have created DDL trigger to protect some important table but could not figure out the way to prevent Truncation on the table.

I know there is only one way to do by enforcing FK constraint but my question is what kind of implication will I face if I implemented this procedure.

Thanks again

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 10

There will be an overhead when deleting rows, and because the dependant table will be empty, that overhead will be minimal.

But If you have control to that process, you could drop the FK at the beginning of the procedure and put it back after the delete/truncate process