Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Database Profiles

Thread: SQL Server table auditing


Permlink Replies: 0 - Pages: 1 Threads: [ Previous | Next ]
Vince Swann

Posts: 169
Registered: 03/27/07
SQL Server table auditing
Posted: Jun 10, 2010 7:22 PM
   thread.click_reply Reply
Not so much a profile or add-on but it is my solution to a feature DBVisualizer doesn't currently have...

Attached is my my T-SQL stored procedure to create auditing tables/triggers for a given table.

My one resides in the master database so I can just execute sp_audit tablename from any database on that server but you can host it in any database you fancy if you invoke it accordingly.

Hope it's of use to someone. Use at own risk.

Invoking it without parameters shows parameter documentation but more complete documentation follows:

====== Purpose ======

**sp_audit** is a stored procedure in the [[master]] database.

It is designed to generate SQL code that can be copy/pasted as another query to generate suditing tables and triggers for a given table in the current database.

====== Details ======

If executed without any params, sp_audit outputs documentation of valid parameters and their purpose.

Otherwise it generates a configurable script either to create or drop the auditing table/trigger for the given table.

The table to be audited must reside within the active database.

The audit table has the same columns as the main table (unless you choose to ignore some via @columns or @ignorecolumns) but adds the following audit columns:

Column
Purpose

audit_hostname
The hostname of the PC that executed the update/delete/insert.

audit_user
The user name used. This will be handy to distinguish web server requests from Uniface requests but once we switch MIS Apps to a single database user this will no longer be useful for determining the actual person responsible.

audit_when
The datatime of the update.

audit_action
The first character is either < (before) or > (after) to show whether the data in the row is before or after the update/delete/insert was performed. The second character shows whether it was an update (U), delete (D) or insert (I).

audit_row
An auto-incremented identity field that can be used to show the order in which operations were performed (as the datatime only has a resolution of 1 second).

===== Inputs ======

The parameters for sp_audit are as follows:

Parameter
Purpose

@tablename
This is the name of the table you want to audit. It must be in the current database.

@audit_tablename
The name of the audit table including database/schema name if needed. Defaults to tablename_Audit in the current database.

@trigger
The name of the audit trigger if needed. Defaults to tablename_AuditTrigger.

@columns
A list of columns to log when changed. If a non-logged column changes nothing is stored in the audit table. If left as NULL, the default is all columns in the table. This list can be seperated by any non-alphanumeric character except underscores.

@ignore_columns
If you want to log all columns except one or two, it may be quicker to only specify the ones you want to ignore. The format is the same as @columns. The default is to ignore nothing.

@expiry_days
If this is set to a number, after any update, delete or insert, any records older than this number of days will be deleted from the audit log. The default is not to delete anything.

@log_old
For updates, this logs the data before the update occurs. The default is 1 (true).

@log_new
For updates, this logs the data after the update occurs. The default is 0 (false).

@drop
If you set this to 1, all parameters except @tablename are ignored and a DROP script is generated to remove the trigger and audit table for the specified table.

All non-required parameters can be ignored or set to null.

===== Outputs ======

The output is a single-column, multiple-row table that contains the auditing generation script.

The first row contains the table creation code.

The 2nd row contains the trigger header code.

The following 3 or 4 rows contain the parts of the trigger that deal with inserts, updates and deletes.

The next row contains the trigger footer code and a comment that contains the SQL code that generated the script for easy re-creation of the script in future.

However, if @drop is set to 1, the above is not generated and instead the first row generated contains the code to drop the trigger and the second row the code to drop the audit table.

Also if @tablename is null or unspecified then basic documentation of parameters is generated instead of a SQL script.

Point your RSS reader here for a feed of the latest messages in all forums