0

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"

1 Answer 1

3

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_SQL_Server_Profiler

2
  • Right on the money as always.
    – jgardner04
    Commented Sep 30, 2009 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
    Commented Sep 30, 2009 at 20:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .