Service broker + trigger based data auditing

Article
1/17/2024

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.

<SourceDb>ProductionDatabase</SourceDb>

<SourceTable>UserTable</SourceTable>

<UserId>UserID</UserId>

</ChangedData>

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

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

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

SET QUOTED_IDENTIFIER ON

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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[uspInsertAuditRec]

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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Atif Sheikh

-- Create date: 24-May-2010

-- Description: Update Detail

-- =============================================

CREATE PROCEDURE [dbo].[uspUpdateDetail]

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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

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

)

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

ALTER DATABASE AuditDatabase

SET ENABLE_BROKER

WITH ROLLBACK IMMEDIATE

Run the following script to create service broker objects in MyProductionDB.

use MyProductionDB

drop master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'onteuhoeu'

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

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)

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

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

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'onteuhoeu'

CREATE MESSAGE TYPE BLOB

VALIDATION = NONE

create CONTRACT BLOB_Contract

(BLOB SENT BY ANY)

CREATE QUEUE BLOB_AdtQueue_Remote1

CREATE SERVICE BLOB_AdtSrv_Remote1

ON QUEUE BLOB_AdtQueue_Remote1

(BLOB_Contract)

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

)

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];

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

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Atif Sheikh

-- Create date: 28-May-2010

-- Description: Create Audit Trigger

-- =============================================

CREATE PROCEDURE [dbo].[uspCreateTriggerToAudit]

@TableName varchar(100)

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

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>

<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

CREATE PROCEDURE [dbo].[uspGetAuditTables]

@pObjectID varchar(max) ,

@pAlias varchar(max) = NULL

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

SET QUOTED_IDENTIFIER ON

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.

Share via

Service broker + trigger based data auditing

Additional resources