Auditing SELECT statements in SQL Server 2008

  • [アーティクル]

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

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Expand Security Node and select Audits.

3. Right click Audit and click ‘New Audit’ to launch the new Audit dialog.

audit

4. 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.

5. Click Ok to create the audit.

T-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)

Creating Database Level Audit Specification:

1. Select the database where the db level audit specification needs to be created.

2. Expand Security under the particular database node and select ‘Database Audit Specifications’

3. Right Click the Database Audit Specifications and select ‘Create new Database Audit Specification’.

dbaudit 

4. 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’.

5. Click ok to create a database level audit specification.

 

T-SQL

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090105-115555]

FOR SERVER AUDIT [Audit_Select_Production_Product]

ADD (SELECT ON OBJECT::[Production].[Product] BY [Peter])

Viewing Audit Logs:

After executing SELECT statements, in order to view the audit logs, follow the below steps

1. Expand Security->Audits-> and select the audit created above.

2. Right click and click ‘View Audit Logs’ from the context menu to launch the audit log viewer dialog.

3. It contains the information about the SELECT statements issued on the particular object.audi log

Comments

  • Anonymous
    January 04, 2009
    PingBack from http://www.codedstyle.com/auditing-select-statements-in-sql-server-2008/

  • Anonymous
    July 09, 2010
    It is nice article. However before we viewing the audit log, the audit created has to be enabled. It si not mentioned here.

  • Anonymous
    July 04, 2011
    what a good article!!!

  • Anonymous
    July 04, 2011
    what a good article!!!

  • Anonymous
    February 15, 2012
    Really a nice article. :)

  • Anonymous
    July 29, 2012
    The comment has been removed

  • Anonymous
    June 29, 2014
    Nice article.

  • Anonymous
    January 27, 2015
    How could i get client_ip_address using auditing in sqlser-2008?

  • Anonymous
    October 28, 2015
    but the result statement not record the full event  just the main parameter if they run SP which i mean not record the full audit.