Tag: Auditing

SQL Server : Auditing

Create a New Audit

The “Audit” object is really just a reference to where the audit file(s) will be located and how they will be managed. It doesn’t define what specific auditing will be done. That is controlled by the server-level and database-level audits explained later.

  • Expand the Management Studio tree for the database of interest. “Security > Audits”.
  • Right-click on “Audits” and select the “New Audit” option.
  • Name the “Audit name” to indicate what it is doing. In this case “ServerAudit”.
  • Decide what should happen on audit failure.
  • Pick the destination (File, Security Log, Application Log). I picked File.
  • If you pick “File”, enter the path and file handling details.
  • Click “OK”.
  • Right-click on the new audit and select the “Enable Audit” option. If you don’t do this you will not be able to write any audit information to the audit location.

Server-Level Audits

  • Expand the Management Studio tree for the server. “Security > Server Audit Specifications”.
  • Right-click on “Server Audit Specifications” and pick the “New Server Audit Specification” option.
  • Set the name. It’s easier if you make this match the Audit you created above.
  • Select the matching “Audit” from the drop-down.
  • Pick the audit action type(s) required.
  • Click “OK”.
  • Right-click on the new server audit specification and select the “Enable Server Audit Specification” option.
  • Right-click on the audit you defined previously (not the server audit specification) and select the “View Audit Logs” option.

You can find an explanation of all the Audit Action Groups and Actions here.

Database-Level Audits

The process is similar to the server-level audits.

  • Create a new Audit as described previously.
  • Expand the specific database of interest in the tree. (Server-Name > Databases > Database-Name > Security > Database Audit Specifications).
  • Right-click on “Database Audit Specifications” and pick the “New Database Audit Specification” option.
  • Set the name. It’s easier if you make this match the Audit you created above.
  • Select the matching “Audit” from the drop-down.
  • Pick the audit action type(s) required. Make them as specific as possible by targeting (Database, Schema or Object) and clicking the “…” buttons to select specific qualifiers.
  • Click “OK”.
  • Right-click on the new database audit specification and select the “Enable Database Audit Specification” option.
  • Right-click on the audit you defined previously (not the server audit specification) and select the “View Audit Logs” option.

You can find an explanation of all the Audit Action Groups and Actions here.

Importing Audits

You can import audit files using the following statement, with the file pattern adjusted accordingly.

SELECT *
FROM sys.fn_get_audit_file (
         'c:\temp\ServerAudit*.sqlaudit',
         Default,
         Default
       )

The “Default” setting mean we will read all files matching the pattern. We can actually set an initial file and offset to limit the files we read. This is explained in the following document.

sys.fn_get_audit_file (Transact-SQL)

We can also create a table holding the files as follows.

SELECT * INTO MyAuditTable
FROM sys.fn_get_audit_file (
         'c:\temp\ServerAudit*.sqlaudit',
         Default,
         Default
        )

SELECT * FROM MyAuditTable

Policy-Based Management

  • Expand the tree. (Server-name > Management > Policy Management).
  • You will see Facets, Conditions and Policies. Facets represent groups of items you can test. Conditions a test itself, using a facet. A policy is a group of one or more conditions. Policies can be scheduled, event-based or on-demand etc.
  • Expand the “Facets” tree node.
  • Double-click on the “Database” facet.
  • Scroll down the list of properties and find AutoShrink. This is what we will test.
  • Richt-click on “Conditions” and select the “New Condition” option.
  • Give it a reasonable name (AutoShrinkCondition).
  • Select the “Database” facet.
  • Enter the Field “@AutoShrink:, the operator “=” and the value “FALSE’.
  • Click “OK”.
  • Right-click on “Policy” and select the “New Policy” option.
  • Give it a reasonable name (AutoShrinkPolicy).
  • Select the condition(s) you want in the policy.
  • Decide how it should be evaluated (on-demand, scheduled etc.).
  • Click “OK”.
  • For an on-demand policy, right-click on the policy and select the “Evaluate” option. The results will let you know if all databases have AutoShrink=FALSE or not.

For more information see: