SQL Server 2008 : Auditing SQL Server - Creating SQL Server Audits with T-SQL

2/24/2011 4:37:52 PM
Before you can define server-level or database-level actions to audit, you must create a SQL Server audit, which is shown in Listing 1.
Example 1. Syntax for Creating a SQL Server Audit

TO { [ FILE (<file_options> [, ...n]) ] | APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ]

As you can see from the syntax, creating a SQL Server audit defines the setup information for an audit. The audit does not contain any information about the actions either at the database level or the server level within its definition. Actually, server-level and database-level audits must be added to a SQL Server audit to define how and where the information is captured and stored. Now, let's take a closer look at the syntax for creating a SQL Server audit.

After you name the audit, determine if you want it written to a file, application log, or security log. If you decide to write the data to a file, then you need to specify the file path and name, the maximum size of the file, the number of rollover files, and if you want to reserve the maximum file size on disk.

The configurable audit options consist of a QUEUE_DELAY, ON_FAILURE, and AUDIT_GUID. The QUEUE_DELAY option sets the time that can pass before an audit action processes. The representation of time is in milliseconds with the minimal and default value of 1000 milliseconds or 1 second. The ON_FAILURECONTINUE and SHUTDOWN. The default value is CONTINUE. The AUDIT_GUID option allows you to specify the globally unique identifier (GUID) of an existing audit for purposes where the GUID needs to be the same from environment to environment. option decides what to do if the target (location of the audit files) is unreachable. The two configurable options are

Once you have determined the settings for your SQL Server audit, then creating an audit is fairly simple and straightforward, as shown in Listing 2.

Example 2. SQL Script That Creates a SQL Server Audit
USE master;



Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone