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, DaveWhat constitutes a "failure" for the purpose of the audit?

-PatP|||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|||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|||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|||Everybody loves the joy of SOX!

If you really, really need to, you can get C2 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_09yd.asp) 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

Is it a good idea to use SQL2005 MDF for audit, exception tracking

I am writing a web application that uses a Teradata database as the primary data source. While Teradata is great as a data warehouse and managing Terabytes of information it doesn't do as well when update or inserting. I was thinking of using a local SQL2005 MDF file to hold a few reference tables and an audit table to collect usage information and exception database to capture any errors.

There could be a few thousand users of the web application but no more than a couple hundred at a time.

I just trying to get some opinions on these technique. I am open to all comments and suggestions.

Thank You

Hi John,

If you're using the SQL Server as the auditing database and exception tracking, I think it will be fine.

Although there will be hundreds of connections to the main app, the exception will not be much and audit data size will not be huge then. Just connect the audit and exception handling module to your SQL Server database, and it will be OK.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Kevin

Thank you. I'm wondering if it is acceptable practice to run SQL Server 2005 express rather than installing a full SQL Server 2005 on the Advanced Server for doing the Audit tracking and exception reporting. The MDF file should never reach the 4GB limit of Express. Thank you again for your answer.