Post reply

Auditing truncates

  • April 24, 2006 at 4:45 am

    #152929

    SQL Server 2000 SP3

    I have a table which has been unexpectedly truncated. I've recreated the data but would like to keep an eye on this table so that if it gets truncated again I'll know who did it and when. I've looked in BOL and Googled, but can't find anything useful. Can anyone help?

    --
    Scott

  • April 24, 2006 at 6:03 am

    #633743

    If they are actually using TRUNCATE TABLE then it isn't logged or won't fire any triggers, so you can't capture it that way.

    You do need to be in sysadmin, dbo,  or ddladmin so that narrows down the users who can run the command...unless of course, admin usernames/passwords are widely known in your organisation which would allow anyone onto the box.

  • April 24, 2006 at 10:39 am

    #633858

    or you could upgrade to 2005 which has ddl triggers.

    seriously though I agree with  Clive - restrict your users who can issue this command.

    You can capture this event with profiler, so a very selective trace would do it.

    or ..

    if you create a schema binding view on the table you will not be allowed to trauncate it.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

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

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