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
–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