| Obtain the list of locally-defined security tables, procedures and functions that require tracking. |
If there are none, this is not a finding.
If neither SQL Server Audit nor SQL Server Trace is in use for audit purposes, this is a finding.
If SQL Server Trace is in use for audit purposes, review the locally-defined security tables for the existence of triggers to raise a custom event on each Update operation. If such triggers are not present, this is a finding.
Verify that all required events are being audited. From the query prompt:
SELECT * FROM sys.traces;
All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding.
Determine the trace(s) being used for the auditing requirement.
In the following, replace # with a trace ID being used for the auditing requirements.
From the query prompt:
SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);
The following required event IDs should be among those listed; if not, this is a finding:
42 -- SP:Starting
43 -- SP:Completed
82-91 -- User-defined Event
162 -- User error message
If SQL Server Audit is in use, proceed as follows.
Verify that all EXECUTE actions on locally-defined permissions-related procedures are being audited. If not, this is a finding.
The basic SQL Server Audit configuration provided in the supplemental file Audit.sql uses the broad, server-level audit action group SCHEMA_OBJECT_ACCESS_GROUP for this purpose. SQL Server Audit's flexibility makes other techniques possible. If an alternative technique is in use and demonstrated effective, this is not a finding.
Determine the name(s) of the server audit specification(s) in use.
To look at audits and audit specifications, in Management Studio's object explorer, expand
>> Security >> Audits
>> Security >> Server Audit Specifications.
>> Databases >> >> Security >> Database Audit Specifications.
Alternatively, review the contents of the system views with "audit" in their names.
Run the following to verify that all UPDATE and EXECUTE actions on any locally-defined permissions tables, procedures and functions are being audited:
SELECT * FROM sys.server_audit_specification_details WHERE server_specification_id =
(SELECT server_specification_id FROM sys.server_audit_specifications WHERE [name] = '')
AND audit_action_name = 'SCHEMA_OBJECT_ACCESS_GROUP';
If no row is returned, this is a finding.
If the audited_result column is not "SUCCESS" or "SUCCESS AND FAILURE", this is a finding.