Change Auditor for SQL Server 7.2

Introduction

You can search, report and alert on changes to all SQL instances being audited or changes made to a specific SQL instance, SQL database, or SQL Server object.


	NOTE: As a best practice, you should not have both SQL and SQL Data Level subsystems defined in a single search. When the SQL subsystem is included without filters, all SQL Data Level events will be included.

Create custom SQL searches

The following scenarios explain how to use the What tab to create custom SQL searches.

To search all SQL instances:

NOTE: If you wanted to, you can use the other search properties tabs to define additional criteria:

•

Who - allows you to search for events generated by a specific user, computer or group

•

Where - allows you to search for events captured by a specific agent or within a specific domain or site

•

When - allows you to search for events that occurred within a specific date/time range

•

Origin - allows you to search for events that originated from a specific workstation or server


	NOTE: Selecting the Private folder will create a search that only you can run and view, whereas selecting the Shared folder will create a search which can be run and viewed by all Change Auditor users.

Open the Searches page.

In the explorer view (left pane), expand and select the folder where you want to save your search.

Click New to enable the Search Properties tabs across the bottom of the Searches page.

On the Info tab, enter a name and description for the search.

Open the What tab, expand Add and select Subsystem | SQL.

On the Add SQL Instance dialog, select All SQL Instances.

Click OK to save your selection and close the dialog.

Once you have defined the search criteria, you can either save the search definition or run the search.

•

To save the search definition without running it, click Save.

•

To save and run the search, click Run.

When this search is run, Change Auditor will search for the SQL events based on the search criteria specified on the What tab and display the results in a new search results page.

To search a specific SQL instance, database or object:

Open the Searches page.

In the explorer view (left pane), expand and select the folder where you want to save your search.

Click New to enable the Search Properties tabs across the bottom of the Searches page.

On the Info tab, enter a name and description for the search.

Open the What tab, expand Add and select Subsystem | SQL.

On the Add SQL Instance dialog, select This Object.

When the This Object option is selected, you must fill in at least one of the following fields to define the SQL objects to include in the search:

•

Instance - Enter the name of the SQL instance or click the browse button to the far right to select from a list. Selecting the browse button will display the Select a SQL Instance and Database dialog which provides a list of SQL instances and associated databases from which you can select the instance and database to be used. If you leave this field blank, Change Auditor will search for SQL events based on the entries made in the DB and/or Object fields for all audited SQL instances.

•

DB - Enter the name of the SQL database to be used or use the browse button to the far right to select from a list. Selecting the browse button will display the Select a SQL Instance and Database dialog which provides a list of SQL instances and associated databases from which you can select the instance and database to be used. If you leave this field blank, Change Auditor will search for SQL events based on the entries made in the Instance and/or Object fields for all audited SQL databases.

•

Object - Enter a SQL Server object to be included in the search definition. If you leave this field blank, Change Auditor will search for SQL events based on the entries made in the Instance and/or DB fields for all audited SQL Server objects.

Once you have specified the SQL instance, database and/or object to be included in the search definition, click Add to add it to the Selection list at the bottom of the dialog.


	NOTE: Select the Exclude the Above Selection(s) check box if you want to search for changes to all SQL instances EXCEPT those listed in the ‘what’ list.


	NOTE: Select the Runtime Prompt check box on this dialog to prompt for a SQL instance every time the search is run.

Once you have selected the SQL instance, database and/or object to be included in the search, click OK to save your selection and close the dialog.

Once you have defined the search criteria, you can either save the search definition or run the search.

•

To save the search definition without running it, click Save.

•

To save and run the search, click Run.

When this search is run, Change Auditor will search for the SQL events based on the search criteria specified on the What tab and display the results in a new search results page.

To search for a SQL instance that already has an audited SQL event in the database:

Open the Searches page.

In the explorer view (left pane), expand and select the folder where you want to save your search.

Click New to enable the Search Properties tabs across the bottom of the Searches page.

On the Info tab, enter a name and description for the search.

Open the What tab, expand Add with Events and select Subsystem | SQL.

On the Add SQL Instance dialog, select an instance from the list and click the Add button to add it to the selection list at the bottom of the page.

Click OK to save your selection and close the dialog.

Once you have defined the search criteria, you can either save the search definition or run the search.

•

To save the search definition without running it, click Save.

•

To save and run the search, click Run.

When this search is run, Change Auditor will search for the SQL events based on the search criteria specified on the What tab and display the results in a new search results page.

Once you have defined the search criteria, you can either save the search definition or run the search.

•

To save the search definition without running it, click Save.

•

To save and run the search, click Run.

When this search is run, Change Auditor will search for the SQL events based on the search criteria specified on the What tab and display the results in a new search results page.

Create custom SQL Data Level searches

The following scenarios explain how to use the What tab to create custom SQL Data Level searches.

NOTE: If you wanted to, you can use the other search properties tabs to define additional criteria:

•

Who - allows you to search for events generated by a specific user, computer or group

•

Where - allows you to search for events captured by a specific agent or within a specific domain or site

•

When - allows you to search for events that occurred within a specific date/time range

•

Origin - allows you to search for events that originated from a specific workstation or server

To search a specific SQL Data Level object:

Open the Searches page.

In the explorer view (left pane), expand and select the folder where you want to save your search.

Click New to activate the Search Properties tabs across the bottom of the Searches page.

On the Info tab, enter a name and description for the search.

Open the What tab, expand Add and select Subsystem | SQL Data Level.

On the Add SQL Data Level Object, select one of the following and enter the search term:

•

•

•

•

Once you have specified the search term, click Add to add it to the Selection list at the bottom of the dialog.


	NOTE: Select the Exclude the Above Selection(s) check box if you want to search for changes to all SQL Data Level events EXCEPT those that match the specified criteria.

Click OK to save your selection and close the dialog.

Once you have defined the search criteria, you can either save the search definition or run the search.

•

To save the search definition without running it, click Save.

•

To save and run the search, click Run.

When this search is run, Change Auditor will search for the SQL Data Level events based on the search criteria specified on the What tab and display the results in a new search results page.

Disabled SQL Events

This section provides an alphabetical list of the SQL events that are disabled by default in Change Auditor. If you want to audit for these events, use the Audit Events page on the Administration Tasks tab to enable these events.

Table 4. Disabled SQL events
Event Class disabled by default	Facility
Audit Add Login	SQL Security Audit Event
Audit Drop Login	SQL Security Audit Event
Audit Login Change	SQL Security Audit Event
Audit Change Audit - Audit Started	SQL Security Audit Event
Audit Change Audit - Audit Stopped	SQL Security Audit Event
Audit Create Object with Derived Permission	SQL Security Audit Event
Audit Drop Object with Derived Permission	SQL Security Audit Event
Audit Schema Object Access	SQL Security Audit Event
Audit Server Alter Trace	SQL Security Audit Event
Auto Stats - Async Completed	SQL Performance Event
Auto Stats - Async Queued	SQL Performance Event
Auto Stats - Async Starting	SQL Performance Event
Auto Stats - Sync	SQL Performance Event
Blocked Process Report	SQL Errors and Warnings Event
Broker: Message Classify - Delayed	SQL Broker Event
Degree of Parallelism - Delete	SQL Performance Event
Degree of Parallelism - Insert	SQL Performance Event
Degree of Parallelism - Select	SQL Performance Event
Degree of Parallelism - Update	SQL Performance Event
Error Logged	SQL Errors and Warnings Event
Event Logged	SQL Errors and Warnings Event
Exception	SQL Errors and Warnings Event
Exec Prepared SQL	SQL TSQL Event
Execution Warnings - Query Timeout	SQL Errors and Warnings Event
Execution Warnings - Query Wait	SQL Errors and Warnings Event
Lock: Acquired	SQL Locks Event
Lock: Cancel	SQL Locks Event
Lock: Escalation	SQL Locks Event
Lock: Released	SQL Locks Event
Lock: Timeout	SQL Locks Event
Lock: Timeout (timeout > 0)	SQL Locks Event
Object: Altered - Begin	SQL Objects Event
Object: Altered - Rollback	SQL Objects Event
Object: Altered - Commit	SQL Objects Event
Object: Created - Begin	SQL Objects Event
Object: Created - Commit	SQL Objects Event
Object: Created - Commit	SQL Objects Event
Object: Deleted - Begin	SQL Objects Event
Object: Deleted - Rollback	SQL Objects Event
Object: Deleted - Commit	SQL Objects Event
Performance Statistics - Cache Query Destroyed	SQL Performance Event
Performance Statistics - New Batch SQL Text	SQL Performance Event
Performance Statistics - Queries in Ad Hoc Statement Compiled	SQL Performance Event
Performance Statistics - Queries in Stored Procedure Compiled	SQL Performance Event
Prepare SQL	SQL TSQL Event
QN:Dynamics - Clock Run Finished	SQL Query Notifications Event
QN:Dynamics - Clock Run Started	SQL Query Notifications Event
QN:Dynamics - Master Cleanup Task Finished	SQL Query Notifications Event
QN:Dynamics - Master Cleanup Task Started	SQL Query Notifications Event
RPC:Completed	SQL Stored Procedures Event
RPC:Starting	SQL Stored Procedures Event
Scan:Started	SQL Scan Event
Scan:Stopped	SQL Scan Event
Showplan All	SQL Performance Event
Showplan All for Query Compile	SQL Performance Event
Showplan Statistics Profile	SQL Performance Event
Showplan Text	SQL Performance Event
Showplan Text (Unencoded)	SQL Performance Event
Showplan XML	SQL Performance Event
Showplan XML for Query Compile	SQL Performance Event
Showplan XML Statistics Profile	SQL Performance Event
SQL:BatchCompleted	SQL TSQL Event
SQL:BatchStarting	SQL TSQL Event
SQL:FullTextQuery	SQL Performance Event
SQL:StmtCompleted	SQL TSQL Event
SQL:StmtRecompile - Deferred Compile	SQL TSQL Event
SQL:StmtRecompile - Set Option Changed	SQL TSQL Event
SQL:StmtRecompile - Statistics Changed	SQL TSQL Event
SQL:StmtStarting	SQL TSQL Event
SQLTransaction Begin	SQL Transactions Event
SQLTransaction Commit	SQL Transactions Event
SQLTransaction Rollback	SQL Transactions Event
SQLTransaction Savepoint	SQL Transactions Event
SP:CacheHit - Compplan Hit	SQL Stored Procedures Event
SP:CacheHit - Execution Context Hit	SQL Stored Procedures Event
SP:CacheMiss	SQL Stored Procedures Event
SP:Completed	SQL Stored Procedures Event
SP:Recompile - Recompile DNR	SQL Stored Procedures Event
SP:Recompile - Set Option Changed	SQL Stored Procedures Event
SP:Recompile - Statistics Changed	SQL Stored Procedures Event
SP:Starting	SQL Stored Procedures Event
SP:StmtStarting	SQL Stored Procedures Event
TransactionLog	SQL Transactions Event
Unprepare SQL	SQL TSQL Event
User Error Message	SQL Errors and Warnings Event
XQuery Static Type	SQL TSQL Event

Documentos relacionados

The document was helpful.

Seleccionar calificación

I easily found the information I needed.

Seleccionar calificación

Seleccione su producto:

Con el fin de ofrecerle un mejor servicio, complete el campo Motivo del chat:

Soluciones recomendadas para su problema

Change Auditor for SQL Server 7.2 - User Guide

Introduction

Create custom SQL searches

Create custom SQL Data Level searches

Disabled SQL Events