Prevent or log Truncate Table commands

Question

Prevent or log Truncate Table commands

SQLHeap

Hall of Fame

Points: 3572
More actions
March 20, 2012 at 11:45 am

#254993

We just had someone who truncated a production table and I can't figure out who did it, and of course no one is owning it. The server is standard edition so Auditing is not an option, how can I prevent or log this in the future?
There is an exception to every rule, except this one...

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Well... You could prevent it by not letting people have access to production databases who shouldn't or are incompetent 😉

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Don't give users DDL Admin or above permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLHeap Hall of Fame Points: 3572 More actions · Answer 3

While I agree that limited access is the best answer, I just arrived and am trying to do just that. Until I sort out the security issues, how can I prevent or at least log that event. I already have DDL triggers in place for all other events. But they don't do truncate.:crying:

There is an exception to every rule, except this one...

Jared SSC Guru Points: 61793 More actions · Answer 4

SQLHeap (3/20/2012)
While I agree that limited access is the best answer, I just arrived and am trying to do just that. Until I sort out the security issues, how can I prevent or at least log that event. I already have DDL triggers in place for all other events. But they don't do truncate.:crying:

Well... In your case, I would immediately change sa password if you think that may be compromised or used and lock down all other logins' access unless needed for the application. Truncating a production table by accident is grounds for a complete lockdown, IMO.

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

DDL triggers won't pick up a truncate, neither will DML triggers.

Have you considered Extended Events?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 6

a serverside trace for DML changes is very lightweight; if you have it rollover you won't have to worry too much about log file sizes,a nd it may help you point the finger at teh guilty party in the future.

also, are you sure it was a truncate command vs a DELETE FROM TABLE command? how do you know it was a truncate table command?

My recommendation echoes the others...remove their DDL_Admin priviledges from the server...

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!

Jared SSC Guru Points: 61793 More actions · Answer 7

Lowell (3/20/2012)
a serverside trace for DML changes is very lightweight; if you have it rollover you won't have to worry too much about log file sizes,a nd it may help you point the finger at teh guilty party in the future.
also, are you sure it was a truncate command vs a DELETE FROM TABLE command? how do you know it was a truncate table command?
My recommendation echoes the others...remove their DDL_Admin priviledges from the server...

Ahh.. Yes! How do you KNOW it was truncate if nobody fesses up to it? Maybe you already looked through your audit?

Jared
CE - Microsoft

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 8

In echoing others, change the sa pwd, Grant only CONNECT/SELECT/SHOWPLAN to the users

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Devil's Bloody Advocate SSC-Addicted Points: 451 More actions · Answer 9

To see if it a truncate table rather than a delete check to see if there is an identity column. If there is, insert a row and if it starts at 1 (or the original seed value) then it was 99% likely a truncate else it was 99% likely to be a delete.

SQLHeap Hall of Fame Points: 3572 More actions · Answer 10

Lowell (3/20/2012)
a serverside trace for DML changes is very lightweight; if you have it rollover you won't have to worry too much about log file sizes,a nd it may help you point the finger at teh guilty party in the future.
also, are you sure it was a truncate command vs a DELETE FROM TABLE command? how do you know it was a truncate table command?
My recommendation echoes the others...remove their DDL_Admin priviledges from the server...

Good point. I don't know because there is no identity column and the recovery model is simple :crying: again. I will check out the trace for DML changes and extended events. Thank you all for your input.

Cheers,

Clay

There is an exception to every rule, except this one...