ALTER SERVER AUDIT (Transact-SQL)

Grein
12/29/2022

Applies to: SQL Server Azure SQL Managed Instance

Alters a server audit object using the SQL Server Audit feature. For more information, see SQL Server Audit (Database Engine).

Transact-SQL syntax conventions

Syntax

ALTER SERVER AUDIT audit_name  
{  
    [ TO { { FILE ( <file_options> [, ...n] ) } | APPLICATION_LOG | SECURITY_LOG } | URL]  
    [ WITH ( <audit_options> [ , ...n] ) ]   
    [ WHERE <predicate_expression> ]  
}  
| REMOVE WHERE  
| MODIFY NAME = new_audit_name  
[ ; ]  
  
<file_options>::=  
{  
      FILEPATH = 'os_file_path'   
    | MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED }   
    | MAX_ROLLOVER_FILES = { integer | UNLIMITED }   
    | MAX_FILES = integer   
    | RESERVE_DISK_SPACE = { ON | OFF }   
}  
  
<audit_options>::=  
{  
      QUEUE_DELAY = integer   
    | ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }   
    | STATE = = { ON | OFF }   
}  
  
<predicate_expression>::=  
{  
    [NOT ] <predicate_factor>   
    [ { AND | OR } [NOT ] { <predicate_factor> } ]   
    [,...n ]  
}  
  
<predicate_factor>::=   
    event_field_name { = | < > | ! = | > | > = | < | < = } { number | ' string ' }

Arguments

TO { FILE | APPLICATION_LOG | SECURITY |URL}
Determines the location of the audit target. The options are a binary file, the Windows application log, or the Windows security log.

Important

In Azure SQL Managed Instance, SQL Audit works at the server level and stores .xel files in Azure Blob Storage.

FILEPATH = 'os_file_path'
The path of the audit trail. The file name is generated based on the audit name and audit GUID.

MAXSIZE =max_size
Specifies the maximum size to which the audit file can grow. The max_size value must be an integer followed by MB, GB, TB, or UNLIMITED. The minimum size that you can specify for max_size is 2 MB and the maximum is 2,147,483,647 TB. When UNLIMITED is specified, the file grows until the disk is full. Specifying a value lower than 2 MB raises MSG_MAXSIZE_TOO_SMALL the error. The default value is UNLIMITED.

MAX_ROLLOVER_FILES =integer | UNLIMITED
Specifies the maximum number of files to retain in the file system. When the setting of MAX_ROLLOVER_FILES=0, there is no limit imposed on the number of rollover files that are created. The default value is 0. The maximum number of files that can be specified is 2,147,483,647.

MAX_FILES =integer
Specifies the maximum number of audit files that can be created. Does not roll over to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated fails with an error.
Applies to: SQL Server 2012 (11.x) and later.

RESERVE_DISK_SPACE = { ON | OFF }
This option pre-allocates the file on the disk to the MAXSIZE value. Only applies if MAXSIZE is not equal to UNLIMITED. The default value is OFF.

QUEUE_DELAY =integer
Determines the time in milliseconds that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default. The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying an invalid number, raises the error MSG_INVALID_QUEUE_DELAY.

ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION}
Indicates whether the instance writing to the target should fail, continue, or stop if SQL Server cannot write to the audit log.

CONTINUE
SQL Server operations continue. Audit records are not retained. The audit continues to attempt to log events and resumes if the failure condition is resolved. Selecting the continue option can allow unaudited activity, which could violate your security policies. Use this option, when continuing operation of the Database Engine is more important than maintaining a complete audit.

SHUTDOWN
Forces the instance of SQL Server to shut down, if SQL Server fails to write data to the audit target for any reason. The login executing the ALTER statement must have the SHUTDOWN permission within SQL Server. The shutdown behavior persists even if the SHUTDOWN permission is later revoked from the executing login. If the user does not have this permission, then the statement will fail and the audit will not be modified. Use the option when an audit failure could compromise the security or integrity of the system. For more information, see SHUTDOWN.

FAIL_OPERATION
Database actions fail if they cause audited events. Actions, which do not cause audited events can continue, but no audited events can occur. The audit continues to attempt to log events and resumes if the failure condition is resolved. Use this option when maintaining a complete audit is more important than full access to the Database Engine.
Applies to: SQL Server 2012 (11.x) and later.

STATE = { ON | OFF }
Enables or disables the audit from collecting records. Changing the state of a running audit (from ON to OFF) creates an audit entry that the audit was stopped, the principal that stopped the audit, and the time the audit was stopped.

MODIFY NAME = new_audit_name
Changes the name of the audit. Cannot be used with any other option.

predicate_expression
Specifies the predicate expression used to determine if an event should be processed or not. Predicate expressions are limited to 3000 characters, which limits string arguments.
Applies to: SQL Server 2012 (11.x) and later.

event_field_name
Is the name of the event field that identifies the predicate source. Audit fields are described in sys.fn_get_audit_file (Transact-SQL). All fields can be audited except file_name and audit_file_offset.
Applies to: SQL Server 2012 (11.x) and later.

number
Is any numeric type including decimal. Limitations are the lack of available physical memory or a number that is too large to be represented as a 64-bit integer.
Applies to: SQL Server 2012 (11.x) and later.

' string '
Either an ANSI or Unicode string as required by the predicate compare. No implicit string type conversion is performed for the predicate compare functions. Passing the wrong type results in an error.
Applies to: SQL Server 2012 (11.x) and later.

Remarks

You must specify at least one of the TO, WITH, or MODIFY NAME clauses when you call ALTER AUDIT.

You must set the state of an audit to the OFF option in order to make changes to an audit. If ALTER AUDIT is run when an audit is enabled with any options other than STATE=OFF, you receive a MSG_NEED_AUDIT_DISABLED error message.

You can add, alter, and remove audit specifications without stopping an audit.

You cannot change an audit's GUID after the audit has been created.

ALTER SERVER AUDIT statement cannot be used inside a user transaction.

Permissions

To create, alter, or drop a server audit principal, you must have ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

Examples

A. Changing a server audit name

The following example changes the name of the server audit HIPAA_Audit to HIPAA_Audit_Old.

USE master  
GO  
ALTER SERVER AUDIT HIPAA_Audit  
WITH (STATE = OFF);  
GO  
ALTER SERVER AUDIT HIPAA_Audit  
MODIFY NAME = HIPAA_Audit_Old;  
GO  
ALTER SERVER AUDIT HIPAA_Audit_Old  
WITH (STATE = ON);  
GO

B. Changing a server audit target

The following example changes the server audit called HIPAA_Audit to a file target.

USE master  
GO  
ALTER SERVER AUDIT HIPAA_Audit  
WITH (STATE = OFF);  
GO  
ALTER SERVER AUDIT HIPAA_Audit  
TO FILE (FILEPATH ='\\SQLPROD_1\Audit\',  
          MAXSIZE = 1000 MB,  
          RESERVE_DISK_SPACE=OFF)  
WITH (QUEUE_DELAY = 1000,  
       ON_FAILURE = CONTINUE);  
GO  
ALTER SERVER AUDIT HIPAA_Audit  
WITH (STATE = ON);  
GO

C. Changing a server audit WHERE clause

The following example modifies the where clause created in example C of CREATE SERVER AUDIT (Transact-SQL). The new WHERE clause filters for the user-defined event if of 27.

ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = OFF)  
GO  
ALTER SERVER AUDIT [FilterForSensitiveData]  
WHERE user_defined_event_id = 27;  
GO  
ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = ON);  
GO

D. Removing a WHERE clause

The following example removes a WHERE clause predicate expression.

ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = OFF)  
GO  
ALTER SERVER AUDIT [FilterForSensitiveData]  
REMOVE WHERE;  
GO  
ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = ON);  
GO

E. Renaming a server audit

The following example changes the server audit name from FilterForSensitiveData to AuditDataAccess.

ALTER SERVER AUDIT [FilterForSensitiveData] WITH (STATE = OFF)  
GO  
ALTER SERVER AUDIT [FilterForSensitiveData]  
MODIFY NAME = AuditDataAccess;  
GO  
ALTER SERVER AUDIT [AuditDataAccess] WITH (STATE = ON);  
GO

Deila með