Systems Engineering and RDBMS

Can I capture a select statement without a trace?

Posted by decipherinfosys on June 19, 2009

This was the question asked by one of our readers:

Hi! Thanks for the wonderful posts on your blog.  I have learnt a lot from the different articles that you guys put out.  I am an Oracle DBA who for the past couple of months have been doing SQL Server DBA work as well.  In Oracle, using FGA (Fine Grained Auditing), I can easily capture a select statement.  In SQL Server, the only way that I have seen so far is to do it via a profiler trace.  Is there any functionality that I am not aware of which will give me the same results?

The answer to this question is Yes – a feature in SQL Server 2008 called “SQL Server Audit” allows us to do audits for the select statements as well but prior to SQL Server 2008, the only way to capture it was using a profiler trace.  Before we start digging into this capability, here is a previous blog post from us in which we had covered the auditing capabilities in Oracle – here and the ones in SQL Server – here.

Let’s see how we can implement this feature.  But before we start, please be aware that this feature is available only in Enterprise Edition.

First, we have to create a server audit object using the “Create Server Audit” command.   This operation needs to be performed in the master database:

USE MASTER
GO
CREATE SERVER AUDIT AUD_DEC_TEST TO FILE(FILEPATH=’C:\DecipherTest\AuditDemo\’)
GO

Now, the next step is to create a “Database Audit Specification” in the database in which we want the monitoring to be done.

USE DECIPHERTEST
GO
CREATE DATABASE AUDIT SPECIFICATION DEC_TEST_AUD FOR SERVER AUDIT AUD_DEC_TEST
ADD (SELECT ON dbo.EMP_ACTIVITY  BY Public)
GO

So, we have now created a database audit specification with a select on the employee activity table and assigned it to the instance level audit object.  As far as the audit groups and the different actions are concerned, there are too many of them to cover here – refer to this MSDN article to get the entire list.

Now, one thing that you need to be aware of that when you create the server audit object and the database audit specification, it is disabled by default which is the right thing since it needs to be a conscious action to put an audit like this in an enabled state.  When we were playing it with first, we had no idea that was the case and could not figure out what the issue was till we started reading the BOL.  So, be aware of it.  You can look at the status using the is_state_enabled column of sys.server_file_audits for the server audit object and sys.database_audit_specifications for the database audit specification.

Let’s go ahead and enable these now:

USE MASTER
GO
ALTER SERVER AUDIT AUD_DEC_TEST WITH (STATE=ON)
GO
USE DECIPHERTEST
GO
ALTER DATABASE AUDIT SPECIFICATION DEC_TEST_AUD WITH (STATE=ON)
GO

So, now that it is enabled, how can we go about reading the data?  We can use the in-built function fn_get_audit_file.  When you look into the file for the first time without it having captured any data, you will see a record which will be for enabling the audit.  Now, let us first run a couple of select statements against our table:

select * from dbo.EMP_ACTIVITY            /*Ran it twice from different sessions*/
select * from dbo.EMP_ACTIVITY WHERE EMP_MASTER_ID = 20

We would expect to see 4 records now when we execute the function – one for the event enabling, 2 for the first statement and 1 for the statement with the filter condition.

SELECT statement, *
FROM fn_get_audit_file (‘C:\DecipherTest\AuditDemo\*’,NULL, NULL)

And here is the abridged output:

Audit_Select

See, how easy it is to capture it now in SQL Server 2008?  Be aware though about the overhead that this might cause on your servers – we have not done any benchmarks (yet) to see what the impact might be…will post the results when we do.  Read up on the technet article mentioned in the references section below to learn more about this wonderful feature in SQL Server 2008.

Have a great weekend guys.

Resources:

  • TechNet article – here.
  • Audit Action Groups and Actions – here.

4 Responses to “Can I capture a select statement without a trace?”

  1. Atiq Rahman said

    The audit is not displaying the values used in the where clause. How can we configure the audit so that it will capture all the values used in th where clause?

    Thanks and regards.

  2. ben p said

    any update on how to see the where cluase values? along the same lines if I audit exec of stored proc will it show input variables?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: