I was assigned task to build our own Data Audit mechanism in SQL Server 2005 last year. There were number of articles and scripts on the internet about asynchronous auditing with service broker. I decided to get the best out of those articles and scripts and create my own trigger + service broker based solution.
Each table assigned for auditing on production database has AFTER INSERT, UPDATE, DELETE trigger. This trigger converts the record(s) of inserted and deleted tables into XML and a message dialog is initiated from the production database. This message is received by the audit database and parsed into specific tables. There will not (or hardly) be any performance issue because of asynchronous messaging between production and audit database.
The example XML is listed below.
<AuditMsg>
  <SourceDb>ProductionDatabase</SourceDb>
  <SourceTable>UserTable</SourceTable>
  <UserId>UserID</UserId>
  <AppUserId />
  <DMLType>U</DMLType>
  <ChangedData>
    <t ID="8521" Name="Atif" Class="SQL Server" />
  </ChangedData>
  <NewRec>
    <t ID="8521" Name="Atif Sheikh" Class="SQL Server" />
  </NewRec>
</AuditMsg>
As you can see from the above XML, it has an element ChangedData which have another element t. t element have attributes depending upon the structure of the table (whose record is changed and the trigger sent the XML message via service broker) of the production database. Same is the case with the attribute NewRec. In the above XML, the UserTable generated this XML. You can see from the XML that the Name is changed from ‘Atif’ to ‘Atif Sheikh’. The XML also holds the information of the production database name and SQL Server UserID, who changed the data of the UserTable. This XML is parsed in audit database. The information in the attributes of ChangedData and NewRec elements is compared and placed in another table tblAudDetail for efficient queries and reports. The parsing of XML is discussed in detail later.
 
Outline of the solution
 The outline of the solution is;

1.     Create New Audit Database

2.     Activate Service Broker on New Audit Database and your production database

3.     Create stored procedure to apply triggers on tables for auditing

 
I will discuss each point in detail.

1.     Create New Audit Database

This database will hold new and old values of columns changed on the production database. Create a new database as AuditDatabase. It will have following Tables;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblAud](
      [tID] [int] IDENTITY(1,1) NOT NULL,
      [xmlQuery] [xml] NULL,
      [Upd_Det] [bit],
      [tDate] [datetime] NULL CONSTRAINT [DF_tblAud_tDate]  DEFAULT (getdate()),
CONSTRAINT [PK_tblAud] PRIMARY KEY CLUSTERED
(
      [tID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO

SET ANSI_PADDING OFF

 

Column xmlQuery of table tblAud will hold XML sent by the trigger of the production database table. Column upd_Det bit column will tell us if XML is parsed and information inserted in tblAudDetail. It will be 1 in case of XML is parsed and information is inserted in tblAudDetail. Column tDate will tell us the date when record was arrived in the tblAud. Considering the above XML example, tblAud will have data as,
 
Here is the structure of tblAudDetail;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAudDetail](
      [AudDetID] [int] IDENTITY(1,1) NOT NULL,
      [tid] [int] NULL,
      [dbName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [TABLENAME] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FieldName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Priorval] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CurrVal] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblAudDetail] PRIMARY KEY CLUSTERED
(
      [AudDetID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
Table tblAudDetail will hold the information of the attributes of elements ChangedData and NewRec from XML in tblAud. You can see from the schema of tblAudDetail that it has columns dbName. It means we can use one audit database for multiple production databases. Considering the above mentioned XML, the tblAudDetail will have following records;
As you can see from the above dataset, only record number 2 have different PriorValue and CurrValue. Secondly, we also need the ID of the UserTable to recognize the record which in row number 1. We don’t need the record number 3. So, according to the code (mentioned ahead), the recordset will be;
 
One record of ID to recognize the record and the second record of the changed information of Column ‘Name’ with prior value of ‘Atif’ and new value of ‘Atif Sheikh’.
 
Here is the structure of tblErrorlog,
 
USE [AuditDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblErrorLog](
      [ErrorNumber] [int] NOT NULL,
      [ErrorSeverity] [int] NULL,
      [ErrorState] [int] NULL,
      [ErrorLine] [int] NULL,
      [ErrorProcedure] [nvarchar](126) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ErrorMessage] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ErrorTime] [datetime] NULL CONSTRAINT [DF__tblErrorL__Error__4865BE2A]  DEFAULT (getdate()),
      [DbUserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [sysUserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
 
Table tblErrorlog will hold details of errors in messages so that XML can be debugged.
 
The audit database will also have three stored procedures.
[dbo].[uspInsertAuditRec]
[dbo].[uspUpdateDetail]
[dbo].[uspUpdateAuditDetailFromAuditTableID]
 
Script for uspInsertAuditRec;
USE [AuditDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspInsertAuditRec]
AS
BEGIN
      SET NOCOUNT ON;  
 
      Begin Try
 
            Declare @ConversationHandle as uniqueidentifier
            Declare @MessageBody as varbinary(max)
            Declare @MessageXML as XML
            Declare @MessageType as sysname
 
            Declare @pBQuery nvarchar(max)     
 
            Begin TRANSACTION
            Print 'Started Receiving';
 
            WAITFOR(
            RECEIVE top (1)
               @MessageType = message_type_name,
               @ConversationHandle = conversation_handle,
               @MessageBody = message_body
            FROM BLOB_AdtQueue_Remote1
            ), TIMEOUT 1000;
           
            Select @pBQuery = convert(nvarchar(max),@MessageBody)
            Select @MessageXML = cast(@pBQuery as XML)
            Declare @sID as INT
            Declare @DBName as nvarchar(100)
            Declare @TableName as nvarchar(100)
           
            IF @MessageType = 'BLOB'
            BEGIN
                  select  @DBName = x.header.value('(//SourceDb)[1]', 'nvarchar(50)') ,
                              @TableName = x.header.value('(//SourceTable)[1]', 'nvarchar(50)')
                             
                  FROM @MessageXML.nodes('//AuditMsg') AS x(header)
                 
                  Insert into tblAud (xmlQuery,upd_det) values (cast(@pBQuery as XML),0)
                  Select @sID = Scope_identity()
 
            END
            ELSE
            BEGIN
                  INSERT INTO tblAud (xmlQuery,upd_det) values ('<a>aaa<a/>','<a>aaa<a/>')
            END
            END CONVERSATION @ConversationHandle
            COMMIT
      End Try
      Begin Catch
                  Rollback
                  print 'In Catch...'                
                  INSERT INTO DBO.tblErrorLog
                  SELECT
                  ERROR_NUMBER() AS ErrorNumber,
                  ERROR_SEVERITY() AS ErrorSeverity,
                  ERROR_STATE() AS ErrorState,
                  ERROR_LINE() AS ErrorLine,
                  ERROR_PROCEDURE() AS ErrorProcedure,
                  ERROR_MESSAGE() AS ErrorMessage,GETDATE(),
                  CONVERT(sysname ,USER_NAME())AS DBUSERNAME,
                  CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME
           
            Insert into dbo.tblErrorXML
                  Select @MessageBody
      End Catch
END
 
The above stored procedure uspInsertAuditRec gets the message from the receiving queue in AuditDatabase. It checks the validity of the message and inserts it into tblAud for further processing. Only BLOB (the message type that we will create soon) message types are processed.
The following stored procedures will parse XML and will mark the record in tblAud;
USE [AuditDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Atif Sheikh
-- Create date:  24-May-2010
-- Description:   Update Detail
-- =============================================
 
CREATE PROCEDURE [dbo].[uspUpdateDetail]
AS
BEGIN
      SET NOCOUNT ON;
      Declare @tid int
      Declare @DbName varchar(100)
      Declare @TableName varchar(100)
      Declare d1 Cursor for Select tid from tblAud where upd_det = 0
      Open d1
      Fetch Next from d1 into @tid
      while @@Fetch_Status = 0
      begin
            PRINT @tid
            select  @DbName = xmlQuery.value('(//SourceDb)[1]', 'varchar(50)') ,
                        @TableName = xmlQuery.value('(//SourceTable)[1]', 'varchar(50)')
            FROM tblAud where tid = @tid
            if @DbName is Not Null and @TableName is Not Null
                  exec uspUpdateAuditDetailFromAuditTableID @tid,@DbName,@TableName
 
            Fetch Next from d1 into @tid
      end  
      Deallocate d1
END
 
The above stored procedure gets record from tblAud that are not processes (Upd_Det = 0) and passes on to the stored procedure uspUpdateAuditDetailFromAuditTableID.
USE [AuditDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Atif-ullah Sheikh
-- Create date: 24-May-2008
-- Description:   Insert into tblAudDetail
-- =============================================
CREATE PROCEDURE [dbo].[uspUpdateAuditDetailFromAuditTableID]
(
      @pID int,
      @pDBName nvarchar(100),
      @pTableName nvarchar(100)    
)
AS
BEGIN
SET NOCOUNT ON;
      Begin Try
            Begin Tran
            Declare @ColName nvarchar(max)
            Declare @Chk1 nvarchar(max)
            Declare @Chk2 nvarchar(max)
            Declare @sSql varchar(max)
            Declare @SQL1 nvarchar(max)  
            Declare @SQL nvarchar(max)   
            Declare @pDef nvarchar(max)  
            Declare @sSql2 varchar(max)
            Declare @SelectCols varchar(max)
            Declare @SelectCols2 varchar(max)
            Declare @ObjId int
 
            Set @sql1 = N'Select @ObjId = Object_ID from ' + @pDBName + '.sys.objects where name = ''' + @pTableName + ''''
            Set @pDef = N'@ObjId varchar(100) OUTPUT'
            Exec sp_executesql @sql1 ,@pDef,@ObjId=@ObjId OUTPUT
           
            Set @sql = 'Declare col2 Cursor for Select name from ' + @pDBName + '.sys.columns where Object_Id = '+ cast(@ObjID as varchar(10))+ ' and system_type_id not in (34,35,99,241,165,173)'
            print(@sql)
            Exec (@sql)
            Open col2
 
            Declare @XMLvar   XML
            Select @XMLvar = xmlQuery from tblAud where tid = @pID
            Set @sql = ''
            Declare @SQL2 varchar(max)
            Declare @SQL3 varchar(max)
            Declare @SQL4 varchar(max)   
            Set @sql2 = ''
            Set @sql3 = ''
            Set @sql4 = ''
 
            Declare @cname varchar(100)
            Fetch next From Col2 into @cname
            Set @sql3 = '(Order By x.header.value(''@' + @cname + ''',''varchar(100)'')) '
            Set @sql = 'Declare @XMLvar   XML
                              Select @XMLvar = xmlQuery from tblAud where tid = ' + cast(@pID as varchar(10))+ '
                              Select * into ##ctbl from ( Select Row_Number() Over '
 
            while @@Fetch_Status = 0
            begin      
                  if @Sql2 <> '' Set @Sql2 = @Sql2 + ','
                  Set @Sql2 = @Sql2 + 'x.header.value(''@' + @cname + ''', ''varchar(50)'') ' + @cname + '_d'
 
                  if @Sql4 <> '' Set @Sql4 = @Sql4 + ','
                  Set @Sql4 = @Sql4 + 'x.header.value(''@' + @cname + ''', ''varchar(50)'') ' + @cname + '_i'  
                  Fetch next From Col2 into @cname
            end
 
            Set @sql = @sql + @sql3 + ' d_rid,' + @sql2
            Set @sql = @sql + ' FROM @XMLvar.nodes(''//ChangedData/t'') AS x(header)) xx
                                          Left OUter join (
                                          select  Row_number() over ' + @sql3 + ' i_rid,' + @sql4 + '
                                          FROM @XMLvar.nodes(''//NewRec/t'') AS x(header))yy
                                          on yy.i_rid = xx.d_rid '
            print(@sql)
            Deallocate Col2
            Exec (@sql)
           
            Declare @dval varchar(100)
            Declare @ival varchar(100)
            Declare @rid int
 
            Set @sql = 'Declare col2 Cursor SCROLL for Select name from ' + @pDBName + '.sys.columns where Object_Id = '+ cast(@ObjID as varchar(10))+ ' and system_type_id not in (34,35,99,241,165,173)'
            Exec (@sql)
            Open col2
            Declare @vRID int
            Declare Col3 Cursor for Select Case when i_RID is Null then d_RID else i_RID end from ##ctbl
            Open Col3
            Fetch Next from col3 into @rid
            while @@Fetch_Status = 0
            begin
                  Fetch next From Col2 into @cname
                  While @@Fetch_Status = 0
                  begin
                        Set @sql1 = ''
                        Set @sql1 = @sql1 + N' Select @dval1 = '+ @cname + '_d ,@ival1 = '+ @cname +'_i from ##ctbl where case when i_RID is Null then d_rid else i_rid end = ' + cast(@rid as varchar(10))
                        Set @pDef = N'@dval1 varchar(100) OUTPUT, @ival1 varchar(100) OUTPUT'
                        Set @vRID = 0
                        Select @vRID = i_rid from ##ctbl where case when i_RID is Null then d_rid else i_rid end = @rid
                        Exec sp_executesql @sql1 ,@pDef,@dval1=@dval OUTPUT,@ival1=@ival OUTPUT
                        if (IsNull(@dval,'') <> IsNull(@ival,'')) or @cname Like '%ID'
                        begin
                              Insert into tblAudDetail (tid,DBName,TableName,FieldName,PriorVal,CurrVal)
                                    Values (@pID,@pDBName,@pTableName,@cname,case when IsNull(@vRID,0) <> 0 then @dval else @ival end,case when IsNull(@vRID,0) <> 0 then @ival else @dval end)
                        end
                        Fetch next From Col2 into @cname
                  end
                  Fetch FIRST From Col2 into @cname
                  Fetch PRIOR From Col2 into @cname
                  Fetch Next from col3 into @rid
            end
            Deallocate Col2
            Deallocate Col3
            Drop table ##ctbl
            update tblAud set upd_det = 1 where tid = @pID
           
            Commit Tran
     
      End Try
      begin Catch
            Rollback
            INSERT INTO dbo.tblErrorLog
            SELECT
                  ERROR_NUMBER() AS ErrorNumber,
                  ERROR_SEVERITY() AS ErrorSeverity,
                  ERROR_STATE() AS ErrorState,
                  ERROR_LINE() AS ErrorLine,
                  ERROR_PROCEDURE() AS ErrorProcedure,
                  ERROR_MESSAGE() AS ErrorMessage,GETDATE(),
                  CONVERT(sysname ,USER_NAME())AS DBUSERNAME,
                  CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME  
      end catch
END
 
The code of uspUpdateAuditDetailFromAuditTableID needs a little bit of explanation. As you can see in the code, this stored procedure receives a parameter @pID which is the ID of tblAud. Another parameter is @pDBname which is the name of production database. Last parameter is @pTableName which is the name of the table from where it was initialized (production database table). Depending upon the parameters, uspUpdateAuditDetailFromAuditTableID parses the XML. The XML has  <ChangedData > and <NewData> elements. The <ChangedData> element holds the information of the original data (before change). <NewData> holds the information of new data (after change). Both these data sets are inserted into ##ctbl and then, checked column by column to get the changed information. Then <ChangedData> element and <NewRec> element holds the complete record. This is also illustrated in the above XML example. This is done via trigger using inserted and deleted memory tables. Here we can decide which column(s) are changed and initialize our next table tblAudDetail with the changed data only as discussed in the XML example above.  I am also inserting values of columns having ‘ID’ in their names. I am performing this to recognize the record based on the primary key. I am assuming here that the primary column name will have the ‘ID’ in its name.

2.     Activate Service Broker on New Audit Database and your own database

Next step is to activate service broker on both databases.
ALTER DATABASE MyProductionDB
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO
 
ALTER DATABASE AuditDatabase
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO
 
 
Run the following script to create service broker objects in MyProductionDB.
use MyProductionDB
drop master key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'onteuhoeu'
GO
 
The master key is created. According to MSDN, “The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.”
CREATE MESSAGE TYPE BLOB
VALIDATION = NONE
GO
 
A message type defines the name of a message and the validation that Service Broker performs on messages that have that name. Both sides of a conversation must define the same message types. BLOB is the name of the message type we are defining.
 
CREATE CONTRACT BLOB_Contract
(BLOB SENT BY ANY)
GO
 
A contract defines the message types that are used in a Service Broker conversation and also determines which side of the conversation can send messages of that type. Each conversation follows a contract. The initiating service specifies the contract for the conversation when the conversation starts. The target service specifies the contracts that the target service accepts conversations for.
 
CREATE QUEUE BLOB_Queue_Init
GO
 
Queues store messages. When a message arrives for a service, Service Broker puts the message on the queue associated with the service.
 
CREATE SERVICE BLOB_Service_Init
ON QUEUE BLOB_Queue_Init
(BLOB_Contract)
 
A Service Broker service is a name for a specific task or set of tasks. Service Broker uses the name of the service to route messages, deliver messages to the correct queue within a database, and enforce the contract for a conversation.
 
 
Run the following script to create service broker objects in AuditDatabase. Same types of objects as above are created on AuditDatabase. But with different names;
 
Use AuditDatabase
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'onteuhoeu'
GO
 
CREATE MESSAGE TYPE BLOB
VALIDATION = NONE
GO
 
create CONTRACT BLOB_Contract
(BLOB SENT BY ANY)
GO
 
CREATE QUEUE BLOB_AdtQueue_Remote1
GO
CREATE SERVICE BLOB_AdtSrv_Remote1
 
ON QUEUE BLOB_AdtQueue_Remote1
(BLOB_Contract)
GO
 
 
We need to alter the queue BLOB_AdtQueue_Remote1 to set its status to ON and associate procedure to be executed when a message is inserted in a queue.
 
ALTER QUEUE BLOB_AdtQueue_Remote1
WITH ACTIVATION
(
    STATUS = ON,
    PROCEDURE_NAME = dbo.uspInsertAuditRec      ,
    MAX_QUEUE_READERS = 5,
    EXECUTE AS OWNER
)
GO
 
MAX_QUEUE_READERS specifies the maximum number of instances of the activation stored procedure that the queue starts simultaneously. The value of max_readers must be a number between 0 and 32767. I have set this value to 5 to avoid contention.
 
The security principal that owns the initiating service must have SEND permission on the target service. So we need to execute,
 
GRANT SEND ON SERVICE::BLOB_Service_Remote1 TO [Public];
GO
 
The SEND permissions are currently granted to the [Public]. Everyone can send message using this queue. I have done this for simplicity. You can specify a specific user to restrict the message sending via this service.
 
 

3.     Create stored procedure to apply triggers on tables for auditing

 

Next we will create stored procedures to apply triggers on the tables we need to Audit on the production database.

 
Use MyProductionDB
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Atif Sheikh
-- Create date: 28-May-2010
-- Description:   Create Audit Trigger
-- =============================================
 
CREATE PROCEDURE [dbo].[uspCreateTriggerToAudit]     
      @TableName varchar(100)
AS
BEGIN
      SET NOCOUNT ON;
      Declare @SCHEMA varchar(100)
      Declare @colnames varchar(max)
      Declare @objid int
      Declare @sql varchar(max)
 
      Select @objid = object_id from sys.objects where name = @tableName
      Select @TABLENAME = table_name, @Schema = Table_Schema from information_schema.tables where table_name = @tableName
 
      Select @colnames = COALESCE(@colnames+', ','')+ '['+ sys.columns.name +']'
                                    from sys.columns where object_id = @objid
                                    and system_type_id not in (34,35,99,241,165,173)
     
      --Select * from sys.types
 
      SET @SQL='CREATE TRIGGER ['+@TABLENAME+'_AuditTrigger]
         ON  ['+@SCHEMA+'].['+@TABLENAME+']
         AFTER UPDATE, INSERT, DELETE
      AS
      BEGIN
 
 
            SET NOCOUNT ON;
 
 
            DECLARE @auditBody varchar(max)
            DECLARE @auditBody1 varchar(max)
            DECLARE @AppUserID varchar(max)
            DECLARE @Objectid bigint     
            DECLARE @DMLType CHAR(1)
            Set @auditBody1 = ''''
            Set @auditBody = ''''
 
            Set @AppUserID = ''''
           
            Select @Objectid = parent_object_id from sys.objects where name = ''' + @tableName + '_AuditTrigger''
            if Exists(Select column_id from sys.columns where object_id = @Objectid and name = ''UpdatedBy'')
            begin
                  --Select @AppUserID = UpdatedBy from inserted as ii
                  Set @AppUserID = ''''
            end              
 
 
            IF NOT EXISTS (SELECT * FROM inserted)
            BEGIN
                  SELECT      @auditBody = (select '+ @colnames +' FROM deleted AS t FOR XML AUTO)
                  Select @DMLType = ''D''
            END
            -- after update or insert statement
            ELSE
            BEGIN      
                  IF EXISTS (SELECT * FROM deleted)
                  begin
                        SELECT      @auditBody = (select '+ @colnames +' FROM deleted AS t FOR XML AUTO)
                        SELECT      @auditBody1 = (select '+ @colnames +' FROM Inserted AS t FOR XML AUTO)
                        SELECT      @DMLType = ''U''
                  end
                  ELSE
                  begin
                        SELECT      @auditBody = (select '+ @colnames +' FROM inserted AS t FOR XML AUTO)
                        SELECT      @DMLType = ''I''
                  end
            END  
            SELECT @auditBody =
                  ''<AuditMsg>
                        <SourceDb>'' + DB_NAME() + ''</SourceDb>
                        <SourceTable>' + @tableName + '</SourceTable>
                        <UserId>'' + SUSER_SNAME() + ''</UserId>
                        <AppUserId>'' + @AppUserID + ''</AppUserId>
                        <DMLType>'' + @DMLType + ''</DMLType>
                        <ChangedData>'' + CAST(@auditBody AS NVARCHAR(MAX)) + ''</ChangedData>''
            if @DMLType = ''U''
                        SELECT @auditBody = @auditBody + ''<NewRec>'' + @auditBody1 + ''</NewRec>''
 
            SELECT @auditBody = @auditBody + ''</AuditMsg>''                 
 
            if @auditBody <> ''''
            begin
                  DECLARE @h UNIQUEIDENTIFIER
                  Declare @CXml varchar(max)
                 
                  Set @CXml = @auditBody
 
                  BEGIN DIALOG CONVERSATION @h
                  FROM SERVICE BLOB_Service_Init
                  TO SERVICE ''BLOB_AdtSrv_Remote1''
                  ON CONTRACT BLOB_Contract
                  WITH ENCRYPTION=OFF     ;
                 
                  SEND ON CONVERSATION @h
                  MESSAGE TYPE BLOB (CONVERT(VARBINARY(max), @CXml))
            end        
      END'
      print @SQL
      set ANSI_NULLS ON
      set QUOTED_IDENTIFIER ON
      exec (@SQL)
END
 
The above stored procedure [dbo].[uspCreateTriggerToAudit] is created on the production database and it accepts parameter @TableName on which trigger is created on the fly. This stored procedure holds the generic dynamic sql. This sql will be used to create a trigger on the tables we need to audit. I will further explain later.
 
The following stored procedure on production database takes the comma separated list of object_id of tables we want to audit on Production database.
Use MyProductionDB
go
CREATE PROCEDURE [dbo].[uspGetAuditTables]
      @pObjectID  varchar(max)      ,
      @pAlias varchar(max) = NULL
AS
BEGIN
 
      SET NOCOUNT ON;
 
      Declare @val varchar(1000)
      Declare @val2 varchar(1000)
      Declare @tab as Table (ObjectID bigint, Alias nvarchar(1000))
 
      Insert into @tab
            Select * from tblAuditTables
 
      delete from tblAuditTables
     
      insert into tblAuditTables (ObjectID,Alias)
                  select a.value,b.value from dbo.fnSplit(@pObjectID,',') a, dbo.fnSplit(@pAlias,',') b
                  where a.tid = b.tid
     
      Declare C1 Cursor For select name from dbo.fnSplit(@pObjectID,',') inner join sys.objects on sys.objects.Object_id = value
      Open C1
      Fetch Next from C1 into @val
      while @@Fetch_Status = 0
      begin
            print(@val)
            if not Exists(Select object_id from sys.objects where name = rtrim(ltrim(@val)) + '_AuditTrigger' and Type = 'TR')
            begin
                  Set @val = rtrim(ltrim(@val))
                  exec uspCreateTriggerToAudit @val
            end
            Fetch Next from C1 into @val
      end
 
      Close C1
      Deallocate C1
 
      Declare C1 Cursor For
      Select sys.objects.name,sys.schemas.name from sys.objects inner join sys.schemas on sys.schemas.schema_id = sys.objects.schema_id where Object_id in
      (Select objectid from @tab
      Except
      Select objectid from tblAuditTables)
     
      Declare @sSql varchar(1000)
     
      Open C1
      Fetch Next from C1 into @val,@val2
      while @@Fetch_Status = 0
      begin      
            Set @sSql = 'Drop Trigger [' + @val2 + '].[' + @val + '_AuditTrigger]'       
            Exec (@sSql)
            Fetch Next from C1 into @val,@val2
      end  
      Close C1
      Deallocate C1
 
END
 
The above stored procedure gets the names of the tables we need to audit. Here we execute the dynamic sql in uspCreateTriggerToAudit according to the object_ID passed to it. For example we have a table UserTable (object_id = 1011235) in the production database which we need to audit. To apply the trigger on this table, we will call the above mentioned stored procedure as;
exec [dbo].[uspGetAuditTables] ‘1011235’, ‘UserTable’
 
On executing you will see the trigger on the UserTable.
The following table tblAuditTables holds the list of tables that we are auditing. I did this so that we can have a quick check of the tables we are auditing.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAuditTables](
      [ObjectID] [int] NOT NULL,
      [Alias] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblAuditTables] PRIMARY KEY CLUSTERED
(
      [ObjectID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
This table only keeps the record of the tables that are being audited.
Now change any value in UserTable. It will be reflected in tblAud as XML in xmlQuery column. You can create reports on Audit data from Audit database according to your requirements. I have created the Insert / Update /Delete statements of the production database from the AuditDatabase.
 
Conclusion
After implementing the above solution, you will be able to track all the changes on the tables on which audit is applied. Also, you can filter out the tables of production database on which you don’t want to apply audit. Due to asynchronous messaging, performance will not be hurt due to the use of triggers. There is also no point of data loss as all the messages are maintained by SQL Server itself.
This long solution might have some drawbacks. I am currently using it on one of my systems in QA without any error (till now). And we are planning to ship it to production server. Any comments / suggestions are most welcome.