Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I'm in an unpleasant situation where I have a requirement to audit any queries executed by users via SSMS, SQLCMD, etc. on a SQL Server 2005 database.

The situation is made worse by the inability to upgrade the version of SQL Server, change server permissions, etc.

I'm currently auditting user logons under priveleged accounts (previous question: Audit 'sa' login in SQL Server 2005).

I'm concerned that now I have a requirement to audit what queries are being run that I will need to set up server-side traces to a table, which will introduce a higher performance cost.

I've hit up Google for some recommendations but can't seem to find anything other than 'run server-side traces / profiler'.

What other options (if any) do I have here? Will I have to bite the bullet and set up highly filtered server side traces to capture this data?

share|improve this question

1 Answer

up vote 2 down vote accepted

Unfortunately there isn't any Change Data Capture, Change Tracking (both coming only from SQL 2008 +) or even enough Trace Events or Trace Event Groups for Use with Event Notifications.

You're stuck with server side traces for saving all the user queries.

share|improve this answer
Thanks @Marian. I have used trace events at least in the short term, because it can capture the cache insert and cache miss events. As we're primarily worried about ad hoc queries, this will presumably capture at least the first instance of the query being run. If the hit rate is too low we'll bump it up to server side traces. – WT_W Nov 22 '12 at 2:22

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.