By: Edwin Sarmiento | Comments (3) | Related: > 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
- Have a look at auditing other server-level or database level actions or action groups such as failed logins, modification of logins and permissions, etc. These are the most common events that are being audited on any IT infrastructure, not just the database server.
- Take a look at these valuable tips
About the author
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
