Take the 2-minute tour ×
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.

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

2 Answers 2

up vote 3 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

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

share|improve this answer
    
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 Dec 8 '13 at 9:01

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.