Change Auditor for SQL Server 7.0.2 - User Guide

SQL Server Auditing

Introduction

The SQL Auditing page on the Administration Tasks tab displays details about each SQL Auditing template created and allows you to add new auditing templates or modify and delete templates. It will initially contain an entry for the Best Practice SQL Auditing Template that ships with Change Auditor.

1
Select Start | All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.
2
Select the SQL Server Services node.
3
Right-click SQL Server (MSSQLSERVER) and select Properties.
4
Select the Advanced tab and locate the Startup Parameters parameter.
5
Add ;-T1906 to the end of the string.
6
Restart the SQL Server (MSSQLSERVER) service.

SQL Auditing page

The SQL Auditing page is displayed when SQL Server is selected from the Auditing task list in the navigation pane of the Administration Tasks tab. From this page you can start the SQL Auditing wizard to specify the SQL instances and the operations to audit. You can also edit existing templates and remove templates that are no longer being used.

The SQL Auditing page contains an expandable view of all the SQL Auditing templates that have been defined. Initially, only the Best Practice SQL Auditing template will be listed on this page. To add a new template to this list, click the Add tool bar button. Once added, the following information is provided for each template:

Click the expansion box to the left of the Template name to expand this view and display the following details for each template:

SQL Auditing templates

To enable SQL Server auditing, you must add a SQL Auditing template to an agent configuration, which can then be assigned to the appropriate agents. Change Auditor for SQL ships with a pre-defined template that you can use to audit key events on the default SQL server instance or you can create a template to specify the SQL instances and SQL Server operations to audit.

The Best Practice SQL Auditing template is a pre-defined template that audits the default SQL instance for the following SQL Server operations:

You can assign this template to an agent configuration or can use it as a base for creating your own SQL auditing templates.

For instruction on how to assign the default Best Practices SQL Auditing template to an agent configuration, see Getting Started.

2
Click Auditing.
3
Select SQL Server (under the Applications heading in the Auditing task list) to open the SQL auditing page.
4
Click Add to start a wizard which steps you through the process of creating a SQL auditing template.
Select the Default option to audit the default instance. Select Add to add it to the SQL Instance list.
Select the Named option to audit a named instance. Select the browse button, select a SQL instance from the list displayed and click OK to close the dialog. Click Add to add the SQL instance to the auditing list.
Select All Instances to audit all the SQL instances on the server. Click Add to add it to the SQL Instance list.
Select an entry from the list box at the top of the page, expand the Add button and click one of the following commands:
Use the Add | Add This Event button to add individual events.
Use the Add | Add All Events in Facility option to add all events in the selected facility.
In the Filter where fields, enter the operator and value to use in the filter. In the first field (left) use the drop-down menu to select the operator (e.g., Like or Not Like; =, !=, <= or >=). The operators listed are based on the entry selected in the Filters list above. In the second field (right) enter the value or string to use in the filter.
Click Add to add it to the Filter list.
NOTE: To add multiple filters, select the column filter row after which the new filter is to be added, and then use the Filter where fields to specify the new criteria. By default, when multiple filters are specified these filters are ‘ANDed’ together and all filters must be met in order to be considered a match. To use the ‘OR’ operator instead, click in the left-most column of a column filter row and select OR from the drop-down. When filters are ‘ORed’ together, then only one of the filters must be met in order to be considered a match.

When both ‘AND’ and ‘OR’ operators are present in the filter list, ‘ORed’ filters are evaluated first and their results are used by the ‘AND’ filter.

9
To create the template and assign it to an agent configuration, expand Finish and select Finish and Assign to Agent Configuration.
On the Agent Configuration page, select the agents assigned to use the modified agent configuration and click Refresh Configuration to ensure the agents are using the latest configuration.
3
Click Finish or expand Finish and select Finish and Assign to Agent Configuration.

Disabling a template allows you to temporarily stop auditing the specified SQL instance without having to remove the auditing template or individual SQL instance from a template.

Place your cursor in the Status cell for the template to be disabled, click the arrow control and select Disabled.
The entry in the Status column for the template will change to ‘Disabled’.
2
To re-enable the auditing template, use the Enable option in either the Status cell or right-click menu.
Place your cursor in the Status cell for the SQL instance to be disabled, click the arrow control and select Disabled.
The entry in the Status column for the selected SQL instance will change to ‘Disabled’.
2
To re-enable the auditing of a SQL instance, use the Enable option in either the Status cell or right-click menu.
相关文档