Prior to SQL Server 2008, the only way to audit the SELECT statements is to use SQL Server Profiler or server side trace. Now using SQL Server 2008 Enterprise, auditing feature is used to audit on SELECT statements. We cannot use triggers as triggers are not fired on SELECT statements.
Audit object is used to monitor various sever and database level events without the need of full trace.
We need to create an Audit object and the Database Level Audit Specification in order to monitor when a SELECT statement is issued against a particular table.
Creating an Audit
- Open SQL Server Management Studio (SSMS) and connect to SQL Server.
- Expand Security Node and select Audits.
- Right click Audit and click ‘New Audit’ to launch the new Audit dialog.
- We need to enter Audit name and the audit destination.
Audit destination can be of Application Log event, Security Log Event, File (or folder). In case of file, a path needs to be entered and the directory should exist. Also need to configure Maximum rollover and maximum file size properties.
- Click Ok to create the audit.
T-SQL:
[sql]
CREATE SERVER AUDIT [Audit_Select_Production_Product]
TO FILE ( FILEPATH = N’c:\temp\selectAudit’
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF)
WITH
(QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE)
[/sql]
Creating Database Level Audit Specification:
- Select the database where the db level audit specification needs to be created.
- Expand Security under the particular database node and select ‘Database Audit Specifications’
- Right Click the Database Audit Specifications and select ‘Create new Database Audit Specification’.
- We need to enter audit name and need to select the server level audit which we created above.Also need to select Audit Action type, object and principal. The audits are logged only when the particular principal name executes a SELECT statement. In case if the audit needs to be logged for every one issuing a SELECT statement, then the principal name should be ‘public’.
- Click ok to create a database level audit specification.
T-SQL
[sql]
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090105-115555]
FOR SERVER AUDIT [Audit_Select_Production_Product]
ADD (SELECT ON OBJECT::[Production].[Product] BY [Peter])
[/sql]
Viewing Audit Logs:
After executing SELECT statements, in order to view the audit logs, follow the below steps
- Expand Security->Audits-> and select the audit created above.
- Right click and click ‘View Audit Logs’ from the context menu to launch the audit log viewer dialog.
- It contains the information about the SELECT statements issued on the particular object.
