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


























































   Got a SQL tip?
            We want to know!

SQL Server 2008 TSQL Auditing Commands for SELECT statements

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (3)   |   Related Tips: More > Auditing and Compliance

Problem
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?

Solution
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
CREATE SERVER AUDIT testAudit TO FILE(FILEPATH='C:\Temp\SelectAudit\')
GO

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
GO
CREATE DATABASE AUDIT SPECIFICATION testAudSpec FOR SERVER AUDIT testAudit
ADD (SELECT ON HumanResources.Employee  BY Public)
GO

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
GO

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

-- Turn on Audit
USE master
ALTER SERVER AUDIT testAudit WITH (STATE=ON)
GO
USE AdventureWorks
ALTER DATABASE AUDIT SPECIFICATION testAudSpec WITH (STATE=ON)
GO

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



Last Update: 12/30/2008


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, September 03, 2009 - 6:15:34 AM - dreic Read The Tip

 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]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090812-100551]
FOR SERVER AUDIT [MAaudit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SELECT ON [dbo].[Transakcija.Kartica] BY public),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = OFF)
GO

 

 

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.

 


Friday, December 21, 2012 - 2:13:19 AM - karthik Read The Tip

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

 

SQL Server Error Code : 33075.

 

Regards,

Karthik

 


Friday, December 21, 2012 - 10:58:41 AM - bass_player Read The Tip

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



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



Comments
Get free SQL tips:

*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 | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.