4

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • 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
    Commented Nov 22, 2012 at 2:22
1

The SQL Server Audit feature provides more details about the SQL Server instance and database events than the Change Tracking and Change Data Capture features - these do not track executed queries. The feature provides the information about who, what and when, and enables granular filtering of the audited events. It also tracks execution of the SELECT statement. Three different output files types are available, all stored in the central location for easier management

Database-level actions provide more granular filtering than server-level ones. You can find the complete list of all database-level audit action groups and events on MSDN - SQL Server Audit Action Groups and Actions

For additional tips and descriptions you can check the Methods for auditing SQL Server data changes series of online articles

Improve this answer
1
  • Unfortunately this isn't available in SQL Server 2005, and as I mentioned I was unable to upgrade the version of SQL Server. It seems like the link only includes SQL Server 2012 functionality also. As much as I wanted to upgrade the version it wasn't an option !
    – WT_W
    Commented Dec 8, 2013 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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