Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Auditing SELECT statements in SQL Server 2008

MSSQLTips author K. Brian Kelley By:   |   Read Comments (6)   |   Related Tips: More > Auditing and Compliance

Problem
Management has asked that I audit when data is read from a table in the database. I can't use triggers, because they don't fire on SELECT statements. Is there a method other than running a SQL Server Profiler or server side trace to audit when SELECTs are issued against tables?

Solution
Prior to SQL Server 2008, the only option is the trace or the use of some 3rd party product. With SQL Server 2008 Enterprise Edition, the Audit object and a database-level Audit Specification can monitor when a SELECT statement is executed against a particular table.

The first thing to do is to create an Audit object using SQL Server Management Studio (SSMS). Audit objects are located under Security and then Audits. If you right-click on Audits you can choose New Audit from the pop-up menu. The Create Audit dialog window is shown in Figure 1.

Figure 1

You'll need to specify the audit name as well as the destination. There are 3 possible destinations: the Application event log, the Security event log, or a file folder. If you want to write to the Security event log, there are some additional steps you might need to perform. These are documented in Books Online.

For this example we'll choose a file path and specify a folder of C:\Temp\SelectAudit. This folder must already exist. Click OK to create the Audit object.

Once you've created an Audit object, you'll need to create an Audit Specification in the appropriate database. Let's setup to monitor the HumanResources.Employee table in the AdventureWorks2008 database. Database audit specifications are located under Security and Database Audit Specifications within the database. By right-clicking on Database Audit Specifications, we get a pop-up menu where we can select the option to create a new Database Audit Specification. A dialog window like what is shown in Figure 2 will appear.

Figure 2

Once again you'll need to enter a name. You'll also need to point it at a particular Audit object. In this case, point it at Audit_Select_HumanResources_Employee. Then you'll need to configure an Audit Action Type. For this Audit Specification, choose SELECT for the Audit Action Type, OBJECT for the Object Class, and HumanResources.Employee for the Object Name. If you want to audit for anyone who may issue a SELECT against this table, choose the public role as the Principal to audit. Click OK to create the new Database Audit Specification.

Once the Audit and the Database Audit Specification are created, you'll need to enable them. This is done by right-clicking on the Audit or the Database Audit Specification and selecting the "Enable..." option. You should get a dialog window which tells you the Audit or Database Audit Specification was enabled successfully. Once both are enabled, auditing on the HumanResources.Employee table has been configured.

If you execute a SELECT statement against HumanResources.Employee, the Audit object should show that event. Right-click on the Audit object and select View Audit Logs. You should see an entry like in Figure 3, which shows the details, to include who executed the query and the exact query that was run.

Figure 3

On a closing note, the Audit object is only available with SQL Server 2008 Enterprise Edition. Prior versions of SQL Server do not have this feature. Versions of SQL Server 2008 less than Enterprise Edition also do not have this feature. However, if you have Enterprise Edition installed, the Audit object can give you a lot of flexibility to monitor certain server or database-level events without a full trace.

Next Steps

  • If auditing SELECT statements is one of your needs, look into using this new feature.  Although the cost difference of using Standard versus Enterprise is quite hefty it may be a less expensive option than other third party tools.
  • Take a look at these other SQL Server 2008 tips
  • Stay tuned for future auditing and SQL Server 2008 related tips


Last Update: 12/18/2008


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Sunday, December 21, 2008 - 11:21:47 AM - prabhupr70 Read The Tip

Few Questions

1. Can I script this (like SQL Server Jobs), so that I can apply the same for different database object as a BATCH file

2. Will this show when I run Object Dependency List (http://blogs.techrepublic.com.com/datacenter/?p=486)

3. How do I specify the File Name (Prefix/Suffix)?


Monday, December 22, 2008 - 2:58:47 PM - bass_player Read The Tip

There is an article coming up which is the Transact-SQL equivalent of what was done here. Let's just wait when that comes out, hopefully soon :-)

I would just like to highlight that there is an option to apply this on a schema instead of a table so that you can capture SELECT queries on all the objects - whether tables or views alike - that belong to the schema you selected.  Of course, as always, your requirement would determine how SQL Audit can be figured.


Tuesday, December 30, 2008 - 8:51:55 AM - bass_player Read The Tip

Here's the article on SQL Server 2008 T-SQL Auditing Commands for SELECT statements


Wednesday, October 12, 2011 - 10:10:28 PM - Rake0 Read The Tip

The biggest Issue I have with SQL 2008 Auditing is that it does not give you the application, or the hostname performing the actions. What a shame, so I can tell my company which loginname performed select, insert, update & delete's but not which application (if applicable) or which hostname. I hope they fix this.


Monday, July 30, 2012 - 3:47:09 AM - Sreeni Read The Tip

How could i get select command start & end time(Means execution time of the query) using auditing in sqlserver-2008?


Tuesday, July 09, 2013 - 2:14:14 PM - Davin Read The Tip

Is there a way to tell it to monitor select for all tables and all logins short of creating an entry for each table?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.