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

 

Database documentation

Documentation is the bane of my life you design a fantastic database and someone tells you that they would like to know how it works, so the task begins.

This script will take a look at the database and do the graft for you, if you have been well behaved and added descriptions to objects you will be golden as the script will go over your database and document the thing for you, of you then export the output to Excel you have a nice document to put into the document folder.

Download file : DocumentDatabase.sql as a text file

Database CRUD scripts

The use of a database is always a good idea and not giving users any direct access to the tables is an even better idea but wrapping the tables with stored procedures that you can secure up by group membership is a little bit of a pain, this script will enable you to create all of the CRUD stored procedures, you can control how the script runs via the options.

You can set all of the following:

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

set @createInsert = 1
set @createUpdate = 1
set @createDelete = 1
set @createSelect = 1
set @createSelectByPrimaryKey = 1

set @DisplaySQL = 0
set @ExecuteSQL = 1

Use the @ExecureSQL & @DisplaySQL options to generate a script that you can look at and run later.

Download file : CRUD.sql as a text file