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?
Best Answer
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.