SQL Server 2008 T-SQL command for audit SELECT statements

The SQL audit in SQL Server 2008 has become a first-class server object. As mentioned in the previous article, you can now even audit a SELECT statement query that requires the use of SQL tracing or other Third-party products in previous versions. However, although using a graphical user interface is good for dealing with small tasks, especially when learning something for the first time, doing so may be tedious, especially if you repeat the same task. This article will use the same example as the previous article through similar Transact-SQL statements. If you want to audit, we can assume that audit in the database 10 or more tables, then this will make it easier for you to do. I will also use the file system to store audit objects. We will use the Create SERVER audit to complete this task.

-- Create Server Audit object
USE master
CREATE SERVER AUDIT testAudit TO FILE(FILEPATH='C:\Temp\SelectAudit\')
GO

This server audit object collects the server and/or database-level behavior and behavior groups you want to monitor and creates at the instance level. This also means that if you have many instances running on a single server, you will create a server audit object for each instance. Next, we will create a database audit specification called TESTAUDSPEC in the appropriate database, and in this case we will use the AdventureWorks database. We will use the Create DATABASE AUDIT specification statement to complete 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're doing here is just creating a database audit specification and assigning it to the server audit object we created earlier. We then add the database-level auditable behavior name, which in this case is a SELECT statement, and then captures the queries executed by all users. You can have multiple behavioral or behavioral groups and related database audit specifications. You can also create a server audit specification that can audit server-level behavior or behavior groups, such as tracking successful or failed logins, changing role members, and so on. A list of SQL Server audit behavior groups and behaviors can be obtained from online Help and MSCN.

Because the server auditing object and the database auditing specification are not available by default, we need to make them usable. To verify their status, you can query sys.server_file_audits and

Sys.database_audit_specifications the System object. The is_state_enabled field can tell us 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