SQL Server 2008 TSQL Auditing Commands for SELECT statements

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


In a previous tip on Auditing SELECT statements in SQL Server 2008, you have seen how you can use SQL Server Management Studio to audit SELECT statements in SQL Server 2008. While it is easy to use the graphical user interface to create audit objects, it would be a bit of a challenge if you would want to do this repeatedly.  Are there equivalent Transact-SQL statements to accomplish the same task?


SQL Audit in SQL Server 2008 has become a first-class server object. As presented in the previous tip, you can now audit even a SELECT query which required using SQL trace or some other third-party products in the previous versions. But while using the graphical user interface would be great in dealing with smaller tasks, especially when learning something for the first time, it may be a bit tedious to do so especially if you would repeat the same tasks. This tip will use the same example as presented in the previous tip using the equivalent Transact-SQL statements. This would make it a bit easier if you intend to audit, let's say, 10 or more tables in your database. Let's start by creating a Server Audit object named testAudit. I'll also use the file system to store the audit file. We will use the CREATE SERVER AUDIT statement for this task.

-- Create Server 
Audit object 
USE master 

This Server Audit object will be responsible for collecting server and/or database-level actions and groups of actions that you want to monitor and is created at the instance level. This also means that if you have multiple instances running on a server, you would have to create Server Audit objects per instance. Next, we'll create a Database Audit Specification named testAudSpec in the appropriate database, in this case, we'll use the AdventureWorks database. We will use the CREATE DATABASE AUDIT SPECIFICATION statement for this task.

-- Create an audit specification
USE AdventureWorks
ADD (SELECT ON HumanResources.Employee  BY Public)

What we did here was to simply create a database audit specification and assign it to the server audit object we created earlier. Then, we've added the name of a database-level auditable action, in this example, a SELECT statement, and capture queries executed by all users. You can have multiple actions or action groups associated with a database audit specification. You can also create a Server Audit specification which audits server-level actions or action groups such as tracking of successful or failed logins, change of role memberships, etc. A list of SQL Server Audit Action Groups and Actions is available from MSDN and Books Online.

Since both the server audit object and database audit specification are disabled by default after creation, we need to enable them. To verify their state, you can query the sys.server_file_audits and the sys.database_audit_specifications system objects. The is_state_enabled column would tell us of their state.

-- Verify Audit objects 
if turned on (1 = Enabled)
USE master 
SELECT is_state_enabled,* FROM sys.server_file_audits
USE AdventureWorks 
SELECT is_state_enabled,* FROM sys.database_audit_specifications

query results

To enable the server audit object and the database audit specification, let's run this code:

-- Turn on Audit
USE master 
USE AdventureWorks 

That's it! We have created audit objects in SQL Server 2008 using Transact-SQL. We can even read the audit file using Transact-SQL.

SELECT session_server_principal_name, statement, *
FROM fn_get_audit_file ('C:\Temp\SelectAudit\*',NULL, NULL)


We use the fn_get_audit_file function to retrieve information from the audit file we created earlier in the server audit object. Notice that we used a wildcard - asterisk(*) - to pass the filenames of the audit files generated as we do not know exactly the complete filename. This will read all the audit files available in the folder we have specified in the parameter. We can also read a specific audit file if we know the exact path and filename.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

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

Friday, December 21, 2012 - 10:58:41 AM - bass_player Back To Top (21082)

Check your SQL Server edition. Server-level audit is available on all editions but database-level audit is only available on Enterprise, Developer and Evaluation Editions

Friday, December 21, 2012 - 2:13:19 AM - karthik Back To Top (21073)

When I am trying to do the Audit file enabling i am getting the following error message ..


SQL Server Error Code : 33075.





Thursday, September 3, 2009 - 6:15:34 AM - dreic Back To Top (3988)

 Umm this code is not working for select audit on column.


The error is :

Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'Transakcija.Kartica', because it does not exist or you do not have permission.


SQL code:

USE [xx]

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090812-100551]
ADD (SELECT ON [dbo].[Transakcija.Kartica] BY public),



If i remove .Kartice and only Transakcija is left (database table) all works. I am 100% there is Kartica column on the table and i do have all permission on database.


get free sql tips
agree to terms