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
CREATE SERVER AUDIT audit_name
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;
GO
CREATE SERVER AUDIT exampleAudit
TO FILE
( FILEPATH = 'C:\', MAXSIZE = 1 GB
)
WITH( ON_FAILURE = CONTINUE)
GO
|
