Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Unanswered: Is it possible to audit Failed Insert, Update and Delete statements?

    Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?

    They also want us to track schema changes. Is this possible?

    Thanks, Dave

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What constitutes a "failure" for the purpose of the audit?

    -PatP

  3. #3
    Join Date
    Jan 2003
    Posts
    95
    The statement does not execute and returns an error. I believe I can trap this failures in Profiler, but I'm not sure what type of overhead this would create. Several people have suggested triggers, but I'm not sure a trigger will execute on failed attempts, only successfull insert, updates and deletes.

    If I take the Profiler approach I'm not sure it will show schema changes.

    Dave

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you tell it to, SQL Profiler can track ANYTHING that goes to SQL Server. DML that works or fails, schema changes, and everything else. The question is: How much disk are you willing to dedicate to making this happen?

    With the Profiler running "wide open" on a moderately busy server you are looking at 2-3 Tb of data in a 24 hour period... Once you've collected the data, you need to figure out what (if anything) you are going to do with it!

    The old Chinese adage applies: Be careful what you wish for, you might get it!

    -PatP

  5. #5
    Join Date
    Jan 2003
    Posts
    95
    We will only be monitoring two or three ids. Not sure if a domain group can be monitored, but if so we will monitor at the group level. This is for Sarbanes-Oxley complaince, which is basically very strict management of database systems for financial institutions. My thanks to Enron. Our DBAs are allowed to manage development and model office environments and a consulting company gets to manage production. Sarbanes-Oxley requires we keep an eye on the production DBAs by monitoring their activity. Profiler may not be the best approach. Even though we will be monitoring a small number of ids, SQL Server still needs to perform conditional logic against all user activity to see if the filter criteria is being met. A software tool may produce less overhead.

    Thanks, Dave

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Everybody loves the joy of SOX!

    If you really, really need to, you can get C2 auditing from SQL-2000. There are thousands of auditing combinations, many of which are pretty much designed for exactly what you want to do.

    I'd be hard pressed to recommend a third party product for this use... At least in my opinion, it is likely to be more work than it is worth in the long run.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •