sa_enable_auditing_type system procedure

Specifies which events to include in auditing.

Syntax

sa_enable_auditing_type( types )

Parameters

types
Use this VARCHAR(128) parameter to specify a comma-delimited string containing one or more of the following values:
- all
  enables all types of auditing.
- connect
  enables auditing of both successful and failed connection attempts.
- connectFailed
  enables auditing of failed connection attempts.
- DDL
  enables auditing of DDL statements.
- options
  enables auditing of public options.
- permission
  enables auditing of permission checks, user checks, and SETUSER statements.
- permissionDenied
  enables auditing of failed permission and user checks.
- triggers
  enables auditing of a trigger event.
- xp_cmdshell
  enables auditing of xp_cmdshell invocations.

Remarks

Use sa_enable_auditing_type to specify which types of auditing to include. This system procedure adds the specified events to the current set of audit events. Use sa_disable_auditing_type to remove events from the current set of audit events. These system procedures set the PUBLIC auditing_options database option so the setting is permanent.

Set the PUBLIC auditing database option to On or Off to enable or disable auditing.

By default, all events are audited (types='all'). If you want a smaller set, use the sa_disable_auditing_type system procedure to clear the events you are not interested in; or use the sa_disable_auditing_type system procedure to clear all events and then use the sa_enable_auditing_type system procedure to specify which types of auditing you want.

If the set of events is empty and you set the PUBLIC auditing database option to On, no auditing information is recorded. To re-establish auditing, you must use the sa_enable_auditing_type system procedure to specify which types of information you want to audit.

If you set the PUBLIC auditing database option to Off, then no auditing information is recorded.

Specify the location where events are logged with the audit_log database option.

Privileges

You must have EXECUTE privilege on the system procedure, as well as the SET ANY SECURITY OPTION system privilege.

Side effects

None

Example

The following example enables all auditing:

CALL sa_enable_auditing_type( 'all' );

The following example enables only DDL and triggers auditing:

CALL sa_disable_auditing_type( 'all' );
CALL sa_enable_auditing_type( 'DDL,triggers' );

The following example illustrates another way to enable only DDL and triggers auditing:

CALL sa_disable_auditing_type( 'all' );
CALL sa_enable_auditing_type( 'triggers' );
CALL sa_enable_auditing_type( 'DDL' );