Post reply

SQL AUDIT

  • May 15, 2006 at 11:38 am

    #113023

    It appears that records seem to be mysteriously deleted for a table (SQL 2000 Server running on NT 40 server). 

    Is there a way to audit one table - for updates and deletes? 

     

    I am a beginner pt administrator - so baby steps are appreciated.

  • May 15, 2006 at 12:46 pm

    #637968

    create a trigger against the table..

  • May 15, 2006 at 1:37 pm

    #637993

    After the fact : no.  Unless you buy a log reader which can be quite expansive.  A good backup can also do the job.

  • May 15, 2006 at 2:25 pm

    #638002

    Ok - how about going forward - what is the way to do this?

  • May 15, 2006 at 2:42 pm

    #638007

    What are your goals? Do you want to know IF the data changed, WHEN it changed, or WHO (or what process) changed it? Or do you need to track something else?

    Audit tables populated by "On Delete" triggers can get you IF and WHEN, and maybe WHO/WHAT if your users or applications have distinct logins.

    I recall seeing an article or two on this kind of auditing in SQL Server Central in the past, if you're not familiar with the subject.

    Philip

  • May 15, 2006 at 7:10 pm

    #638039

    Thanks.  My goal is to see when and by who records from on table in the database were deleted and/or changed.

     

    Any ideas are welcome - I will also search for the articles you referenced.

  • May 15, 2006 at 10:09 pm

    #638051

    Got a bit more time now to do a quick scan.

    Steve Jones discussed several auditing techniques in a series of articles (here's the last--for obvious reasons, these articles never link forward, only back):

    http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart3.asp

    Another article with a bit more detail on triggers:

    http://www.sqlservercentral.com/columnists/lPeysakhovich/anaudittrailgenerator.asp

    Philip

  • May 16, 2006 at 12:58 pm

    #638254

    Thanks.  The mirroring in this article so far has been helpful. 

    Next question - it mentions setting up a trigger - once you apply a trigger how would you stop it?

  • May 16, 2006 at 1:02 pm

    #638256

    You don't.  It runs only when data is modified (depending on how you set it up).  Once its job is over, then it stops itself untill next execution.

  • May 16, 2006 at 1:38 pm

    #638261

    Thanks.  If I want it to never run again - can I just delete it or is there anythingi special I need to do?

     

    I saw in Query Analzer that it can be deleted.

  • May 16, 2006 at 1:54 pm

    #638265

    Just delete it :

     

    DROP TRIGGER 'TriggerName'

  • May 16, 2006 at 4:31 pm

    #638305

    Or leave it there, but have your trigger code check for a flag that tells it whether auditing is on or off.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

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