Auditing SELECT statements in SQL Server 2008

By:   |   Comments (6)   |   Related: > Auditing and Compliance


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?


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

create audit

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

create database audit specification

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

log file viewer

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.
  • Stay tuned for future auditing and SQL Server related tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, July 9, 2013 - 2:14:14 PM - Davin Back To Top (25761)

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

Monday, July 30, 2012 - 3:47:09 AM - Sreeni Back To Top (18843)

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

Wednesday, October 12, 2011 - 10:10:28 PM - Rake0 Back To Top (14835)

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.

Tuesday, December 30, 2008 - 8:51:55 AM - bass_player Back To Top (2475)

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

Monday, December 22, 2008 - 2:58:47 PM - bass_player Back To Top (2435)

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.

Sunday, December 21, 2008 - 11:21:47 AM - prabhupr70 Back To Top (2431)

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 (

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

get free sql tips
agree to terms