SQL Server Audit

Who did what and when did they do it?

SQL Servers has some fantastic audit capability but sometimes a client will want to hand craft their own and this script can act as a place to start.

The script will create audit tables that will audit what your users are up to.

The script has some options:

set @Author = SUSER_NAME()
set @SPPrefix = ‘usp_’
set @Schema = ‘dbo’

set @DisplaySQL = 1
set @ExecuteSQL = 0

–@CreationStyle
–1 – Create only audit tables where no audit tables are in existence.
–2 – Create all audit tables by dropping existing audit tables.
–3 – Create all audit tables and place current audit tables into archive e.g. _Archive to _Archive_+@DateStyle

set @CreationStyle = 1

–The date style for the _Archive rename as follows.
set @DateStyle = convert(varchar, getdate(), 9)

Use the different creation styles to hive off audit tables on a regular basis you don’t want the size of the tables to become an issue, run them daily, weekly or monthly depending on the needs of your system & users but archive them off.

There are two scripts for this one, there is the creation script and the drop script, use them as needed.

Download file : Audit.sql as a text file

Download file : DropAudit.sql as a text file