Chat now with support
Chat with Support

Change Auditor for SQL Server 7.1.1 - User Guide

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.
1
Open the Searches page.
2
In the explorer view (left pane), expand and select the folder where you want to save your search.
3
Click New to enable the Search Properties tabs across the bottom of the Searches page.
4
On the Info tab, enter a name and description for the search.
5
Open the What tab, expand Add and select Subsystem | SQL.
6
On the Add SQL Instance dialog, select All SQL Instances.
7
Click OK to save your selection and close the dialog.
8
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.
9
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:
1
Open the Searches page.
2
In the explorer view (left pane), expand and select the folder where you want to save your search.
3
Click New to enable the Search Properties tabs across the bottom of the Searches page.
4
On the Info tab, enter a name and description for the search.
5
Open the What tab, expand Add and select Subsystem | SQL.
6
On the Add SQL Instance dialog, select This Object.
7
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.
8
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.
9
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.
10
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:
1
Open the Searches page.
2
In the explorer view (left pane), expand and select the folder where you want to save your search.
3
Click New to enable the Search Properties tabs across the bottom of the Searches page.
4
On the Info tab, enter a name and description for the search.
5
Open the What tab, expand Add with Events and select Subsystem | SQL.
6
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.
7
Click OK to save your selection and close the dialog.
8
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.
9
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.
10
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.
11
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:
1
Open the Searches page.
2
In the explorer view (left pane), expand and select the folder where you want to save your search.
3
Click New to activate the Search Properties tabs across the bottom of the Searches page.
4
On the Info tab, enter a name and description for the search.
5
Open the What tab, expand Add and select Subsystem | SQL Data Level.
6
On the Add SQL Data Level Object, select one of the following and enter the search term:
•
Application Name
•
Database Name
•
Table Name
•
Transaction ID
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.
7
Click OK to save your selection and close the dialog.
8
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.
9
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

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating