Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a COTS (commercial off the shelf) application that is changing its database schema enough to make existing queries return invalid data. Think "select * from cars where cartype='car'", but now we've added trucks in too .. so you need to do "select * fro mcars where cartype='car' and subtype='car'" to get the actual car results (ignore the insanity of this .. it makes more sense in the obscure actual instance).

Is there a way to audit all SQL statements executed by a given sql server login?

Is there a SQL Server "proxy" that can do this, or other inline modifications (progmatically replace a table with a view)?

Edit

We are looking to capture queries such that I can match them against a rules engine (ie, a perl script) looking for the lack of the second parameter.

ie.. "if a query INCLUDES "cartype" but not "subtype" email me an alert"

share|improve this question

1 Answer 1

up vote 3 down vote accepted

If you're running SQL 2005 or higher there is a default trace already running that gives you a certain level of auditing. Read about it more here: http://www.mssqltips.com/tip.asp?tip=1111

You can also create your own server-side trace to audit what you need. I blogged how to do it here: http://sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/

You can also watch a video on how to use the Profiler tool to see what's going on: http://sqlserverpedia.com/wiki/Using%5FSQL%5FServer%5FProfiler

share|improve this answer
    
Right on the money as always. –  jgardner04 Sep 30 '09 at 18:48
1  
Ha, thanks Jonathan. Also SirStan, to answer your question about performance hit the default trace is so minimal if you didn't know it was running you wouldn't notice. Server-side trace impact depends on how much stuff you audit. ALWAYS run server-side and never through the GUI (in prod) as the GUI will kill performance and you'll have angry users. –  SQLChicken Sep 30 '09 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.