Tony Williams

I needed to create an extensive db audit system for a project at work, little bit of searching lead me to these two articles:

1. Trigger Based Audit
2. Centralized Asynchronous Auditing

How it works

Setting up the Audit Database

USE master

IF DB_ID('[AUDITDB]') IS NULL
	CREATE DATABASE [AUDITDB]		

-- enable service broker
ALTER DATABASE [AUDITDB] SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE [AUDITDB] SET TRUSTWORTHY ON

GO

USE [AUDITDB] 	

GO
-- get service broker guid for [AUDITDB].
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.spSendAuditData stored procedure
SELECT	service_broker_guid
FROM	sys.databases
WHERE	database_id = DB_ID()

GO

IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
	DROP TABLE dbo.MasterAuditTable

GO
-- Master Audit Table
CREATE TABLE MasterAuditTable
(
	AuditID BIGINT IDENTITY(1,1) NOT NULL,
	DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
	DatabaseName nvarchar(128),
	TableName nvarchar(128),
	PrimaryKeyField nvarchar(1000),
	PrimaryKeyValue nvarchar(1000),
	FieldName nvarchar(128),
	OldValue nvarchar(1000),
	NewValue nvarchar(1000),
	UpdateDate datetime DEFAULT (GetDate()),
	UserName nvarchar(128)
)

GO

IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
	DROP TABLE dbo.AuditDialogs

GO
-- Table that will hold dialog id's for each database on the server
-- These dialogs will be reused. why this is a good thing is explained here:
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
CREATE TABLE dbo.AuditDialogs
(
	DbId INT NOT NULL,
	DialogId UNIQUEIDENTIFIER NOT NULL
)

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
	DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
	Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
	ErrorProcedure NVARCHAR(126) NOT NULL,
	ErrorLine INT NOT NULL,
	ErrorNumber INT NOT NULL,
	ErrorMessage NVARCHAR(4000) NOT NULL,
	ErrorSeverity INT NOT NULL,
	ErrorState INT NOT NULL,
	AuditedData XML NOT NULL,
	ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
	DROP PROCEDURE dbo.usp_WriteAuditData

GO

-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
	DECLARE @msgBody XML
	DECLARE @dlgId uniqueidentifier

	WHILE(1=1)
	BEGIN
		BEGIN TRANSACTION
		BEGIN TRY

			-- insert messages into audit table one message at a time
			;RECEIVE top(1)
					@msgBody	= message_body,
					@dlgId		= conversation_handle
			FROM	dbo.TargetAuditQueue

			-- exit when the whole queue has been processed
			IF @@ROWCOUNT = 0
			BEGIN
				IF @@TRANCOUNT > 0
				BEGIN
					ROLLBACK;
				END
				BREAK;
			END 

			DECLARE @DatabaseName NVARCHAR(128), @TableName NVARCHAR(128),
					@DMLType CHAR(1),
					@PrimaryKeyField nvarchar(1000), @PrimaryKeyValue nvarchar(1000),
					@FieldName nvarchar(128), @OldValue nvarchar(1000), @NewValue nvarchar(1000),
					@UpdateDate datetime, @UserName nvarchar(128)

			-- xml datatype and its capabilities rock
			SELECT	@DatabaseName = T.c.query('/AuditMsg/DatabaseName').value('.[1]', 'NVARCHAR(128)'),
					@TableName = T.c.query('/AuditMsg/TableName').value('.[1]', 'NVARCHAR(128)'),
					@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
					@PrimaryKeyField = T.c.query('/AuditMsg/PrimaryKeyField').value('.[1]', 'NVARCHAR(1000)'),
					@PrimaryKeyValue = T.c.query('/AuditMsg/PrimaryKeyValue').value('.[1]', 'NVARCHAR(1000)'),
					@FieldName = T.c.query('/AuditMsg/FieldName').value('.[1]', 'NVARCHAR(128)'),
					@OldValue = T.c.query('/AuditMsg/OldValue').value('.[1]', 'NVARCHAR(1000)'),
					@NewValue = T.c.query('/AuditMsg/NewValue').value('.[1]', 'NVARCHAR(1000)'),
					@UpdateDate = T.c.query('/AuditMsg/UpdateDate').value('.[1]', 'datetime'),
					@UserName = T.c.query('/AuditMsg/UserName').value('.[1]', 'NVARCHAR(128)')
			FROM	@msgBody.nodes('/AuditMsg') T(c)

			INSERT INTO dbo.MasterAuditTable(DatabaseName, TableName, DMLType, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)
			SELECT @DatabaseName, @TableName, @DMLType, @PrimaryKeyField, @PrimaryKeyValue, @FieldName, @OldValue, @NewValue, @UpdateDate, @UserName

			-- No need to close the conversation because auditing never ends
			-- you can end conversations if you want periodicaly with a scheduled job
			-- END CONVERSATION @dlgId

			IF @@TRANCOUNT > 0
			BEGIN
				COMMIT;
			END
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK;
			END
			-- insert error into the AuditErrors table
			INSERT INTO AuditErrors (
					ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
					ErrorSeverity, ErrorState, AuditedData)
			SELECT	ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
					ERROR_SEVERITY(), ERROR_STATE(), @msgBody
		END CATCH;
	END
END

GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataWriter')
	DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'TargetAuditQueue')
	DROP QUEUE dbo.TargetAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
	DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
	DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
	VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
	([//Audit/Message] SENT BY INITIATOR)

-- create the queue to run the spWriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
	WITH	STATUS=ON,
	ACTIVATION (
		PROCEDURE_NAME = usp_WriteAuditData,	-- sproc to run when the queue receives a message
		MAX_QUEUE_READERS = 50,					-- max concurrently executing instances of sproc
		EXECUTE AS 'dbo' );

-- create a target service that will accept inbound audit messages
-- set the owner to dbo
CREATE SERVICE [//Audit/DataWriter]
		AUTHORIZATION dbo
	ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])

Creating/Modifying the database to Audit

USE master
GO

IF DB_ID('[DBToAudit]') IS NULL
	CREATE DATABASE [DBToAudit]

-- enable service broker
ALTER DATABASE [DBToAudit] SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE [DBToAudit] SET TRUSTWORTHY ON

GO
USE [DBToAudit]

GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
	DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'InitiatorAuditQueue')
	DROP QUEUE InitiatorAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
	DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
	DROP MESSAGE TYPE [//Audit/Message]

GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message] 
	VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
	([//Audit/Message] SENT BY INITIATOR)

-- create the initiator queue 
CREATE QUEUE dbo.InitiatorAuditQueue

-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender] 
	AUTHORIZATION dbo
	ON QUEUE dbo.InitiatorAuditQueue	-- no contract means service can only be the initiator

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
	DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
	Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
	ErrorProcedure NVARCHAR(126) NOT NULL,
	ErrorLine INT NOT NULL,
	ErrorNumber INT NOT NULL,
	ErrorMessage NVARCHAR(4000) NOT NULL,
	ErrorSeverity INT NOT NULL,
	ErrorState INT NOT NULL,
	AuditedData XML NOT NULL,
	ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
	DROP PROCEDURE dbo.usp_SendAuditData

GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
	@AuditedData XML
)
AS
BEGIN
	BEGIN TRY
		IF @AuditedData IS NULL
			RETURN
		DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
		SELECT @dlgIdExists = 1

		-- Check if our database already has a dialog id that was previously used
		-- Why reusing conversation dialogs is a good this is explaind here
		-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
		-- very well
		SELECT	@dlgId = DialogId
		FROM	[AUDITDB].dbo.AuditDialogs AD 
		WHERE	AD.DbId = DB_ID()
		IF 	@dlgId IS NULL
		BEGIN 
			SELECT @dlgIdExists = 0
		END

		-- Begin the dialog, either with existing or new Id
		BEGIN DIALOG @dlgId
			FROM SERVICE    [//Audit/DataSender]											   
			TO SERVICE      '//Audit/DataWriter', 
							-- this is a [AUDITDB] Service Broker Id (change it to yours)
							'A688B601-A575-4458-9D7C-1607CD7F540D'
			ON CONTRACT     [//Audit/Contract]
		WITH ENCRYPTION = OFF;

		-- add our db's dialog to AuditDialogs table if it doesn't exist yet
		IF @dlgIdExists = 0
		BEGIN 
			INSERT INTO [AUDITDB].dbo.AuditDialogs(DbId, DialogId)
			SELECT	DB_ID(), @dlgId
		END
		--SELECT @AuditedData

		-- Send our data to be audited
		;SEND ON CONVERSATION @dlgId	
		MESSAGE TYPE [//Audit/Message] (@AuditedData)
	END TRY
	BEGIN CATCH
		INSERT INTO AuditErrors (
				ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage, 
				ErrorSeverity, ErrorState, AuditedData)
		SELECT	ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(), 
				ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
	END CATCH
END

GO

Generating Triggers

The final script is generates a trigger for each table that in turn generates a script to pull the changes made during an insert/update/delete operation and sends it to the store procedure generated in the second script. There is nothing to do here but run it

NOTE: This script hasn’t been adapted to ignore tables that store binary information, if you know how to do this then feel free to fork /submit a patch.

DECLARE @sql varchar(max), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME NOT LIKE 'Audit%'

WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = ''
SELECT @sql = @sql+
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on [' + @TABLE_NAME+ '] for insert, update, delete

as

SET NOCOUNT ON

  declare @bit           int,
          @field         int,
          @maxfield      int,
          @char          int,
          @fieldname     varchar(128),
          @TableName     varchar(128),
          @PKCols        varchar(1000),
          @sql           varchar(2000),
          @UpdateDate    varchar(21),
          @UserName      varchar(128),
          @Type          char(1),
          @PKFieldSelect varchar(1000),
          @PKValueSelect varchar(1000)

  select @TableName = '''+@TABLE_NAME+'''

  select @UserName = system_user,
         @UpdateDate = convert(varchar(8), getdate(), 112) + '' '' +
                       convert(varchar(12), getdate(), 114)

  if exists (select *
             from   inserted)
    if exists (select *
               from   deleted)
      select @Type = ''U''
    else
      select @Type = ''I''
  else
    select @Type = ''D''

  select *
  into   #ins
  from   inserted

  select *
  into   #del
  from   deleted

  select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME +
                   '' = d.'' +
                          c.COLUMN_NAME
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @PKFieldSelect = COLUMN_NAME
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @PKValueSelect = coalesce(@PKValueSelect + ''+'', '''') +
                                 ''isnull(convert(nvarchar(100), coalesce(i.'' +
                                                  COLUMN_NAME + '',d.'' +
                          COLUMN_NAME +
                                 '')),'''''''')''
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  if @PKCols is null
    begin
        raiserror(''no PK on table %s'',
                  16,
                  -1,
                  @TableName)

        return
    end

  select @field = 0,
         @maxfield = max(ORDINAL_POSITION)
  from   INFORMATION_SCHEMA.COLUMNS
  where  TABLE_NAME = @TableName

  while @field < @maxfield
    begin
        select @field = min(ORDINAL_POSITION)
        from   INFORMATION_SCHEMA.COLUMNS
        where  TABLE_NAME = @TableName
               and ORDINAL_POSITION > @field

        select @bit = ( @field - 1 )% 8 + 1

        select @bit = power(2, @bit - 1)

        select @char = ( ( @field - 1 ) / 8 ) + 1

        if substring(COLUMNS_UPDATED(), @char, 1) & @bit > 0
            or @Type in ( ''I'', ''D'' )
          begin
              select @fieldname = COLUMN_NAME
              from   INFORMATION_SCHEMA.COLUMNS
              where  TABLE_NAME = @TableName
                     and ORDINAL_POSITION = @field

             	SELECT @sql =''
             DECLARE @auditBody XML
             	
             	SELECT  @auditBody =
		''''
			'+DB_NAME()+'
			'' + @TableName + ''
			'' + @Type + ''
			'' + @PKFieldSelect + ''
			''''+'' + @PKValueSelect + ''+''''
			'' + @fieldname + ''
			''''+isnull(convert(nvarchar(1000),d.'' + @fieldname + ''), '''''''') + ''''
			''''+isnull(convert(nvarchar(1000),i.'' + @fieldname + ''), '''''''') + ''''
			'' + @UpdateDate + ''
			'' + @UserName+ ''
		''''
		
	''

    SELECT @sql = @sql + '' from #ins i full outer join #del d''

    SELECT @sql = @sql + @PKCols

    SELECT @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname

    SELECT @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' +
                  @fieldname +
                         '' is not null)''

    SELECT @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' +
                  @fieldname +
                         '' is null)

				EXEC dbo.usp_SendAuditData @auditBody
                         ''
    exec( @sql)
end
end 
'


SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME NOT LIKE 'Audit%'
END