When you first start looking on the internet, you will probably find this link: http://www.nigelrivett.net/AuditTrailTrigger.html. The mayor problem with this audit trail is that the table will be huge because it stores the change of every field in a table in a seperate row. For our audit trail we needed a more simple approach: Just a shadow copy of the record which was edited. After some investigation I've found the build in SQL feature called 'Change Data Capture', which is only available in the enterprise edition. This is a little above our budget of 0 euro (as usual in IT it may cost nothing) ;). The other problem I found with Change Data Capture is that it isn't transparent enough. It does it's job, but nobody knows exactly how it works, I find this a bit tricky for our production environment.
So after just some more (2 hours!) searching I've come to the magnificent piece of SQL you'll find below from http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL.
After some minor adjustments it was ready to go!
But wait...
Our website users don't connect directly to the database, they use LINQ to SQL which has the user credentials of our Application Pool, so we still can't see who did the change :( StackOverflow has the answer: http://stackoverflow.com/questions/3197607/how-to-pass-out-of-band-current-user-id-data-to-sql-server-2008. Apparently there is an option to set CONTEXT_INFO on a connection so we can pass some extra data from the website user.
Time to hack LINQ! We want to execute the following statement everytime a new connection is used by the user:
SET CONTEXT_INFO 0x<binaryusername>
First we create a base class for our DataContext on which we fetch all Connection StateChange events. When the connection is opened send the 'SET CONTEXT_INFO' statement.
public class DataContextAuditBase
{
public DataContextAuditBase(string connection) : base(connection)
{
Connection.StateChange += ConnectionStateChange;
}
public DataContextAuditBase(IDbConnection connection) : base(connection)
{
Connection.StateChange += ConnectionStateChange;
}
public DataContextAuditBase(string connection,
MappingSource mappingSource) : base(connection, mappingSource)
{
Connection.StateChange += ConnectionStateChange;
}
public DataContextAuditBase(IDbConnection connection,
MappingSource mappingSource) : base(connection, mappingSource)
{
Connection.StateChange += ConnectionStateChange;
}
private string UserName
{
get { return (HttpContext.Current != null) ? HttpContext.Current.User.Identity.Name : Environment.UserName; }
}
private string StringToHex(string convert)
{
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
return BitConverter.ToString(encoding.GetBytes(convert)).Replace("-","").ToLower();
}
private void ConnectionStateChange(object sender, StateChangeEventArgs e)
{
if( e.CurrentState == ConnectionState.Open)
{
ExecuteCommand("SET CONTEXT_INFO 0x" + StringToHex(UserName));
}
}
}
And change the BaseClass of the DataContext:
Run the newly created website and see the magic happening, right before your eyes!
The used audit trail stored procedure (used to create tables on which you want to use the audit):
/******************************
** Name: sp_GenerateAuditTable
** Desc: creates an audit table in a database of your choosing from a user
** table that you specify in whatever database you run the sproc against
** Original Author: Cedric Baelemans
** Modifying Author: Andrew Tappert (2011-3-17)
** Modifying Author: Luuk Sommers (2012-04-17)
** Date: 2012-04-17
**
** Found on: http://sameproblemmorecode.blogspot.com/2012/04/sql-audit-trail-with-pass-through-user.html
** More info on original: http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL
**************************/
ALTER PROCEDURE [dbo].[sp_GenerateAuditTable]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtension varchar(128) = '_shadow',
@DropAuditTable bit = 0,
@AuditDatabaseName varchar(128) = null
AS
BEGIN
declare @sql nvarchar(4000)
if not exists( SELECT schema_name FROM information_schema.schemata WHERE schema_name = @Owner )
BEGIN
PRINT 'Creating chema [' + @Owner + ']'
set @sql = 'CREATE SCHEMA [' + @Owner + '] AUTHORIZATION [dbo]'
print @sql
EXEC (@sql)
END
-- Check if table exists
IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END
IF @AuditDatabaseName is null
BEGIN
set @AuditDatabaseName = (select DB_NAME())
END
-- Check @AuditNameExtension
IF @AuditNameExtension is null
BEGIN
PRINT 'ERROR: @AuditNameExtension cannot be null'
RETURN
END
-- Drop audit table if it exists and drop should be forced
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
set @sql = 'drop table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
print @sql
EXEC (@sql)
END
-- Declare cursor to loop over columns
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId
OPEN TableColumns
-- Declare temp variable to fetch records into
DECLARE @ColumnName varchar(128)
DECLARE @ColumnType varchar(128)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint
-- Declare variable to build statements
DECLARE @CreateStatement varchar(8000)
DECLARE @ListOfFields varchar(2000)
SET @ListOfFields = ''
declare @AuditTableExists int
declare @existsParams nvarchar(4000)
set @existsParams='@Exists int output'
set @sql = 'if exists (SELECT * FROM [' + @AuditDatabaseName + '].information_schema.tables WHERE table_schema=N''' + @Owner + ''' and table_name=''' + @TableName + @AuditNameExtension + ''') set @Exists=1 else set @Exists=0'
-- Check if audit table exists
print @sql
Exec sp_executesql @sql, @existsParams, @Exists=@AuditTableExists output
print @AuditTableExists
IF @AuditTableExists = 1
BEGIN
-- AuditTable exists, update needed
PRINT 'Table already exists. Only triggers will be updated.'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
-- AuditTable does not exist, create new
-- Start of create table
SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ('
SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
END
IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '
IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + 'NOT '
SET @CreateStatement = @CreateStatement + 'NULL, '
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
-- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,'
SET @CreateStatement = @CreateStatement + '[AuditContext] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'
-- Create audit table
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
print @CreateStatement
EXEC (@CreateStatement)
-- Set primary key and default values
SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditContext] DEFAULT (CAST(CONTEXT_INFO() AS VARCHAR)) for [AuditContext]'
EXEC (@CreateStatement)
END
CLOSE TableColumns
DEALLOCATE TableColumns
/* Drop Triggers, if they exist */
PRINT 'Dropping triggers'
set @sql = '
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Insert]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1)
EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]'')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Update]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1)
EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]'')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Delete]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1)
EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]'')'
exec (@sql)
/* Create triggers */
PRINT 'Creating triggers'
set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Insert ON [' + @Owner + '].[' + @TableName + '] FOR INSERT AS INSERT INTO ['+ @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted'
print @sql
EXEC (@sql)
set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Update ON [' + @Owner + '].[' + @TableName + '] FOR UPDATE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted'
print @sql
EXEC (@sql)
set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Delete ON [' + @Owner + '].[' + @TableName + '] FOR DELETE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted'
print @sql
EXEC (@sql)
END
Use the comments for any issues, questions and of course comments :)
Happy auditing!
Luuk
