Home | About | Resources | Contact | Partners
MUST Logo

Layout for printing

Download Free MUST Trial Today



"MUST allows our business to use Access to flexibly RAD our systems and then easily move production systems to SQL Server"
Karen Spencer, Partner and Business Manager,
Gravitas IT solutions

"FANTASTIC! What was going to take me hours hand (re)coding Stored Procs just worked..."
David Peach, I.T. Management & Support Services,
Qbt Consulting

"It has saved me a lot of time"
Julian Baker,
The Access Man



SQL Server Database Auditing (17th October 2011)

The first question to ask in regards of database auditing is what exactly are you trying to achieve? The reason for asking this question is that auditing can mean a lot of different things to different people. Auditing generally implies recording and monitoring some level of change/access to a database, but it can also imply a need to recover from changes made to the database.

In this article we are going to look at several different approaches to adding different levels of auditing capabilities to a SQL Server database. The methods we are going to look at can be summarised as follows:-

  1. Simple recording of who/when a record was created or last updated
  2. Providing a log over time recording who/when records were created/updated or deleted
  3. Recording the field values which that were changed, together with record key, table and user information. There is also the question of whether to record both the old and new values for any fields, or record only the new values
  4. Recording a before and after image of each row that is updated, or recording only the before image

Comment on Before and After Images of Records or Field Values

Because any current record will contain the latest after change image values for data, the question is do you need to record the after image as part of the audit, or just the before change values (then by looking at either subsequent changes or the current record you can work out the before image of the data). This raises the question of how easy does it need to be when you come to trace through the changes to see what was changed or reconstruct the old data. By recording both the before and after images of data, you increase the required storage and processing to create the log, but reduce the complexity of post-processing when examining the log.

If you record only changes to fields, then any reconstruction of the original record will be quite complicated, whereas if you record an image of the entire record then post comparison will be much simpler, and if required recovery of the data will be simpler when you have an entire image of the record.

Simple Auditing

The simplest way to achieve a basic level of auditing is to add four fields to each table to record when and who creates a record and who and when last modifies a record.

CREATE TABLE [dbo].[Categories](
  [CategoryID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
  [CategoryName] [varchar](15) NOT NULL,
  [Description] [varchar](max) NULL,
  [Picture] [varbinary](max) NULL,
  [TS_Categories] [timestamp] NULL,
  [A_CategoriesCreatedBy] [varchar](30) NULL,
  [A_CategoriesCreatedOn] [datetime] NULL,
  [A_CategoriesUpdatedBy] [varchar](30) NULL,
  [A_CategoriesUpdatedOn] [datetime] NULL
)
GO

ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesCreatedBy]
DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
FOR [A_CategoriesCreatedBy]
GO

ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesCreatedOn]
DEFAULT (getdate()) FOR [A_CategoriesCreatedOn]
GO

ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesUpdatedBy]
DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
FOR [A_CategoriesUpdatedBy]
GO

ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesUpdatedOn]
DEFAULT (getdate()) FOR [A_CategoriesUpdatedOn]
GO

Then we require to add a trigger which will update the record of who last changed the record:-

CREATE TRIGGER [dbo].[TR_Categories_U]
ON [dbo].[Categories]
FOR UPDATE

AS UPDATE [Categories]
  SET [A_CategoriesUpdatedOn] = GetDate(),
  [A_CategoriesUpdatedBy] = SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME()))
  FROM [Categories] s
  INNER JOIN [INSERTED] i ON s.[CategoryID] = i.[CategoryID]
GO

This level of auditing is trivial to add to your database, it has a minimum impact on performance, and provides a quick visual check on who/when a record was changed/created. It does not provide a great deal of information, for example you will not know anything about whether records have been deleted. However, it is quite a useful low cost feature to add to a database.

Simple Auditing Logging Changes Over Time

The next level of auditing could be to extend the first method by recording over time who inserted/modified or deleted records. This can again be added at a very low cost both in terms of performance and storage.

This level of auditing can yield information which is useful to information on the amount of change that individuals make in a system, what kind of information is most frequently changes, and to some extent when data is deleted who deleted the data (although it won’t tell you if some design problem in the application was responsible for inadvertently deleting data, it does provide more information in investigating what may have happened).

To do this you would create a table for recording the log of changes; this table could record the table name, the primary key, the user, the date/time and nature of the change (D-Delete, I–Insert, U- Update). You also need to consider how to hold composite keys, the easiest method would be to add sufficient fields to hold the maximum composite key size in the application. There is also the question of the data types in the key fields; probably the simplest approach would be to hold each key as a text field. In the example below we have arbitrarily select 3 key columns all text 128 characters, you could have a lot of different approaches to this problem.

CREATE TABLE tblAudit(
  AuditID INT IDENTITY(1,1) PRIMARY KEY,
  TableName VARCHAR(128),
  PKColumnValue1 VARCHAR(128),
  PKColumnValue2 VARCHAR(128),
  PKColumnValue3 VARCHAR(128),
  ChangedBy VARCHAR(30),
  ChangedOn DATETIME,
  ChangeType VARCHAR(1)
)
GO

ALTER TABLE [dbo].[tblAudit] ADD CONSTRAINT [def_tblAudit_ChangedBy]
DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
FOR [ChangedBy]
GO

ALTER TABLE [dbo].[tblAudit] ADD CONSTRAINT [def_tblAudit_ChangedOn]
DEFAULT (getdate()) FOR [ChangedOn]
GO

The next step is to write the Trigger code on each table to record the changes to the log table.

CREATE TRIGGER [dbo].[TR_Categories_D]
ON [dbo].[Categories]
AFTER DELETE AS
  INSERT INTO tblAudit(TableName,PKColumnValue1,ChangeType)
  SELECT 'Categories', CategoryID, 'D' FROM DELETED
GO

CREATE TRIGGER [dbo].[TR_Categories_I]
ON [dbo].[Categories]
AFTER INSERT AS
  INSERT INTO tblAudit(TableName,PKColumnValue1,ChangeType)
  SELECT 'Categories', CategoryID, 'I' FROM INSERTED
GO

CREATE TRIGGER [dbo].[TR_Categories_U]
ON [dbo].[Categories]
FOR UPDATE AS
  INSERT INTO tblAudit(TableName,PKColumnValue1,ChangeType)
  SELECT 'Categories', CategoryID, 'U' FROM INSERTED
GO

The following shows the results of an update, insert and delete operation:-



Auditing of entire records to a Separate Database

With this method, the idea is that every time a row is changed a before and after image of the row is written to a separate database.

First you create a new database called in this example called NorthwindAuditing, and then we create a mirror table for the categories data.

CREATE TABLE [dbo].[Categories](
  [Action_Categories] [char](1) NULL,
  [Operation_Categories] [char](1) NULL,
  [CategoryID] [int] NULL,
  [CategoryName] [varchar](15) NULL,
  [Description] [varchar](max) NULL,
  [Picture] [varbinary](max) NULL,
  [TS_Categories] [varbinary](8) NULL,
  [A_CategoriesCreatedBy] [varchar](30) NULL,
  [A_CategoriesCreatedOn] [datetime] NULL,
  [A_CategoriesUpdatedBy] [varchar](30) NULL,
  [A_CategoriesUpdatedOn] [datetime] NULL
) ON [PRIMARY]

GO

This image table has two additional fields called Action_Categories and Operation_Categories, which record details of the change being made to the data.

In the main database we create the following triggers:-

CREATE TRIGGER [dbo].[TR_Categories_D]
ON [dbo].[Categories]
AFTER DELETE AS
  INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'B','D',* FROM DELETED
GO

CREATE TRIGGER [dbo].[TR_Categories_I]
ON [dbo].[Categories]
AFTER INSERT AS
  INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'A','I',* FROM INSERTED
GO

CREATE TRIGGER [dbo].[TR_Categories_U]
ON [dbo].[Categories]
FOR UPDATE
AS UPDATE [Categories]
  SET [A_CategoriesUpdatedOn] = GetDate(),
  [A_CategoriesUpdatedBy] = SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME()))
  FROM [Categories] s
  INNER JOIN [INSERTED] i ON s.[CategoryID] = i.[CategoryID]
INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'B','U',* FROM DELETED
INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'A','U',* FROM INSERTED
GO

The following image shows the effect of updating a record (B indicates the Before Image of the data and A the After Image of the data):-



One advantage of this technique is that the triggers never need to be altered, but the down-side is that you need to make sure that the image tables match the structure of the main tables. When you change a table design in the main database you put the changes into the audit system and the main system at the same time. This method will give very good performance; the cost being that of one or two additional inserts. It is also very simple to view the auditing data as the entire before and after image of each row is held in the auditing tables.

Auditing Field Changes

An alternative approach the previous methods is to only record those fields which have been edited (together with the table name and primary key) in a single table.

The code in this section has been developed and tested for SQL Server 2008.

The Update(ColumnName) function can be used for all columns except computed columns to test whether data in the column has been updated, so you could cycle through each column inside a trigger to establish which rows had been updated or inserted.

The second function to investigate is the COLUMNS_UPDATED(), this will return a bitmask for the ordinal set of fields in the row. This is quite a complicated function to understand and manipulate. Your columns are treated by the Ordinal Number (positional number), columns 1-8 are encoded in the first byte, then columns 9-16 in the second byte.

If you want to test for any changes in the first 8 fields, we use SUBSTRING(COLUMNS_UPDATED(),1,1), for the next 8 columns we would use SUBSTRING(COLUMNS_UPDATED(),1,2), varying the third parameter 1,2... as required.

Within each block the 8 fields are ordered bitwise from left to right, and we can use a bit mask to & out the result. For example the first block of fields can be tested using the following:-

IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 1 = 1
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 2 = 2

Until the last field which is IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 128 = 128

Then we could test the first field in the second block using IF SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1

You can experiment with this function in the management studio by writing a simple trigger and displaying the results:-

CREATE TRIGGER [dbo].[TR_Categories_AU]
ON [dbo].[Categories]
AFTER UPDATE
AS
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 1 = 1
    Print 'Column 1 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 2 = 2
    Print 'Column 2 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 4 = 4
    Print 'Column 3 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 8 = 8
    Print 'Column 4 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 16 = 16
    Print 'Column 5 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 32 = 32
    Print 'Column 6 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 64 = 64
    Print 'Column 7 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 128 = 128
    Print 'Column 8 Updated'
  IF SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1
    Print 'Column 9 Updated'
GO

Then if we execute the following update:-

UPDATE Categories
SET CategoryName = CategoryName
GO

In our example this gives the following output:-

Column 3 Updated
Column 6 Updated

In the above we get two changes because column 6 is a timestamp (automatically updated), and column 3 contains the data we changed.

Now in our example, we actually added in another column at the beginning of the table, and then deleted the column, to illustrate a very important point; you cannot always accept that the bitmap ordering of the fields matches what the order looks like in the management studio. We said our column was number 3, but it looks like it is number 2!



You need to execute the following to find how the numbering matches up between the columns and the bitwise map:-

SELECT ORDINAL_POSITION, COLUMN_NAME,
  COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
  COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories'
GO



Now we can see that the Ordinal_Position of 2 and 5 matches what we see when looking at the table structure, but the column_id which indicates the order in the bitmask is 3 and 6, which matches what was returned by the COLUMNS_UPDATED() function.

This point is made in the SQL Server documentation: -

‘In SQL Server 2008, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view...’

The following code can be used to see how a calculation based around this would work.

DECLARE @OrdinalPosition INT
DECLARE @Bitmask INT
DECLARE @Bit INT
DECLARE @Byte INT

SET @OrdinalPosition = 1
WHILE @OrdinalPosition <= 50
BEGIN
  SELECT @Bit = (@OrdinalPosition-1) % 8 + 1 -- gives 1,2,3,4,5,6,7,8
  SELECT @Bitmask = POWER(2,@Bit - 1) -- gives 1,2,4,8,16,32,64,128
  SELECT @Byte = ((@OrdinalPosition - 1) / 8) + 1 -- gives 1 for for 1-8, 2 for 9 -16 etc.

  PRINT CONVERT(VARCHAR(8),@OrdinalPosition) + Char(9) + CONVERT(VARCHAR(8),@Byte) +
      Char(9) + CONVERT(VARCHAR(8),@Bit) + Char(9) + CONVERT(VARCHAR(8),@BitMask)

  SET @OrdinalPosition = @OrdinalPosition + 1
END

This could then be used with an expression like the following inside a trigger (more complete code to follow):-

IF SUBSTRING(COLUMNS_UPDATED(),@Byte,1) & @Bitmask = @Bitmask -- or > 0
    Print 'Column ' + CONVERT(VARCHAR(8),@OrdinalPosition) + ' Updated'

We are now ready to create our auditing table to record this information, in this example we have decided on a maximum of 3 parts to a primary key, but this is easily extended by adding more fields. We have also choosen to hold the data in a VARCHAR(MAX) data type field:-

CREATE TABLE tblAudit(
  AuditID INT IDENTITY(1,1) PRIMARY KEY,
  SchemaName VARCHAR(128),
  TableName VARCHAR(128),
  PKColumnValue1 VARCHAR(128),
  PKColumnValue2 VARCHAR(128),
  PKColumnValue3 VARCHAR(128),
  FieldName VARCHAR(128),
  FieldValue VARCHAR(MAX),
  ImageType VARCHAR(1), -- 'B' (before),'A' (after)
  ActionType VARCHAR(1), -- 'U' (update), 'D' (delete), 'I' (insert)
  ChangedBy VARCHAR(30),
  ChangedOn DATETIME
)
GO

Then we can use the following trigger for auditing:-

CREATE TRIGGER [dbo].[TR_Categories_AU]
ON [dbo].[Categories]
FOR UPDATE
AS
DECLARE @IDPosition INT -- equivalent to the ordinal position
DECLARE @Bitmask INT -- bit mask for comparing value
DECLARE @Bit INT -- bit position in the byte
DECLARE @Byte INT -- byte number
DECLARE @MaxIDPosition INT -- maximum field number
DECLARE @FieldName VARCHAR(128) -- name of changed field
DECLARE @InsertCommand VARCHAR(1024) -- command for inserting in audit
DECLARE @ChangedBy VARCHAR(128)
DECLARE @ChangedOn VARCHAR(20)
SET @ChangedBy = substring(suser_sname(),charindex(''\'',suser_sname())+(1),len(suser_sname()))
SET @ChangedOn = CONVERT(VARCHAR(20),GetDate()) -- tag all the changes consistently
-- Table holding field names and ColumnID
DECLARE @Fields TABLE(ColumnID INT, ColumnName VARCHAR(128))
INSERT INTO @Fields(ColumnID,ColumnName)
SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') As ColumnID, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories' -- this needs changed for the desired table

SELECT @MaxIDPosition = MAX(ColumnID) FROM @Fields
-- Because we can not execute dynamic sql against INSERTED and DELETED
-- we need a copy of these tables
SELECT * INTO #Deleted FROM deleted
SELECT * INTO #Inserted FROM inserted

SET @IDPosition = 1

WHILE @IDPosition <= @MaxIDPosition
BEGIN
  SELECT @Bit = (@IDPosition-1) % 8 + 1 -- gives 1,2,3,4,5,6,7,8
  SELECT @Bitmask = POWER(2,@Bit - 1) -- gives 1,2,4,8,16,32,64,128
  SELECT @Byte = ((@IDPosition - 1) / 8) + 1 -- gives 1 for for 1-8, 2 for 9 -16 etc.

  IF SUBSTRING(COLUMNS_UPDATED(),@Byte,1) & @Bitmask > 0
  BEGIN
    -- column has changed
    -- Get the fieldname
    SELECT @Fieldname = ColumnName FROM @Fields WHERE ColumnID = @IDPosition
    -- Post the before and after column values
    -- This needs changed for the desired table and primary keys
    -- you can easily add multiple keys here
    -- Our timestamp columns are all named TS_ and we want to exclude them
    IF LEFT(@FieldName,3) <> 'TS_'
    BEGIN
      SET @InsertCommand = 'INSERT INTO tblAudit(SchemaName,TableName, PKColumnValue1, FieldName, FieldValue,         ImageType, ActionType, ChangedBy, ChangedOn)
      SELECT ''dbo'', ''Categories'', CategoryID, ''' + @Fieldname + ''', CONVERT(VARCHAR(MAX),' + @FieldName + '),         ''B'', ''U'',''' + @ChangedBy + ''',''' + @ChangedOn + ''' FROM #deleted'
      EXEC (@InsertCommand)
      SET @InsertCommand = 'INSERT INTO tblAudit(SchemaName,TableName, PKColumnValue1, FieldName, FieldValue,         ImageType, ActionType, ChangedBy, ChangedOn)
      SELECT ''dbo'',''Categories'', CategoryID, ''' + @Fieldname + ''', CONVERT(VARCHAR(MAX),' + @FieldName + '),         ''A'', ''U'',''' + @ChangedBy + ''',''' + @ChangedOn + ''' FROM #inserted'
      EXEC (@InsertCommand)
    END
  END
  SET @IDPosition = @IDPosition + 1
END
GO

This method unfortunately has to take a snapshot of the DELETED and INSERTED virtual tables, because you are not allowed inside the trigger to execute dynamic sql which refers to the actual INSERTED and DELETED tables. We also need to have the table name and the primary key hard-coded into the trigger (the primary key could be worked out, but there would be a cost to doing that); later we will see how to avoid that problem by dynamically writing the trigger code.

A nice feature of this approach is you only need to do this once unless you intend to change the primary keys on the table. The TSQL is also not too complicated so that adding multiple part keys or making other changes is not difficult.

Shown below is an example of the resulting audit log:-



Automating Field Auditing Level Trigger Code

Revised 13th November 2011, code modified to allow for spaces in both field names, and primary key field names.

With a little bit of generalisation we can write a process which will write our trigger code, and use a similar process to record when rows are inserted and deleted, code for this is included in the download at the end of this article for all three insert, update and delete triggers. You may consider not using the Insert triggers, because these records will be held in the actual tables.

The example below will generate a set of triggers for the UPDATE trigger.

-- ************************************************************************
-- Examples of automatically generating the auditing triggers for UPDATE
-- ************************************************************************

DECLARE @SchemaName VARCHAR(128) -- schema name
DECLARE @TableName VARCHAR(128) -- table name
DECLARE @KeyName VARCHAR(128) -- primary key part
DECLARE @KeyCount INT -- columns in the primary key
DECLARE @cmd VARCHAR(8000) -- cmd string to execute
DECLARE @KeyListInsert VARCHAR(1024) -- list of keys to audit values to
DECLARE @KeyList VARCHAR(1024) -- list of keys to be used
DECLARE @MaxKeys INT -- Maximum number of parts to a key
DECLARE @TriggerName VARCHAR(128) -- Name for the trigger

SET @MaxKeys = 3 -- change for different maximum number of parts in a key

DECLARE Table_Cursor CURSOR
FOR
  SELECT TABLE_SCHEMA,TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_NAME <> 'tblAudit'

OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @SchemaName , @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE Key_Cursor CURSOR
  FOR
  SELECT u.COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
      ON u.TABLE_NAME = c.TABLE_NAME
      AND u.TABLE_SCHEMA = c.TABLE_SCHEMA
      AND u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
  WHERE c.TABLE_SCHEMA = @SchemaName and c.TABLE_NAME = @TableName
  AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'

  SET @KeyCount = 0
  -- PKColumnValue1, PKColumnValue2, PKColumnValue3
  SET @KeyListInsert = ''
  SET @KeyList = ''
  OPEN Key_Cursor
  FETCH NEXT FROM Key_Cursor INTO @KeyName

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @KeyCount = @KeyCount + 1
    IF @KeyCount > @MaxKeys
    BEGIN
      PRINT @SchemaName + ' ' + @Tablename + ' number of keys exceeded ' + CONVERT(VARCHAR(8),@KeyCount)
      CLOSE Key_Cursor
      DEALLOCATE Key_Cursor
      GOTO SkipTheTable
    END
    IF @KeyCount > 1
    BEGIN
      SET @KeyListInsert = @KeyListInsert + ','
      SET @KeyList = @KeyList + ','
    END
    SET @KeyListInsert = @KeyListInsert + 'PKColumnValue' + CONVERT(CHAR(1),@KeyCount)
    SET @KeyList= @KeyList + '[' + @KeyName +']'
    FETCH NEXT FROM Key_Cursor INTO @KeyName
  END
  CLOSE Key_Cursor
  DEALLOCATE Key_Cursor

  SET @TriggerName = '[' +@SchemaName + '].[TR_' +Replace(@TableName,' ','') + '_AU]'
  IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''+@TriggerName +''))
  BEGIN
    SET @cmd = 'DROP TRIGGER ' +@TriggerName
    EXEC (@cmd)
  END
  -- Now create the trigger body
  SET @cmd = 'CREATE TRIGGER ' + @TriggerName + '
ON [' + @SchemaName + '].[' + @TableName + ']
FOR UPDATE
AS
DECLARE @IDPosition INT -- equivalent to the ordinal position
DECLARE @Bitmask INT -- bit mask for comparing value
DECLARE @Bit INT -- bit position in the byte
DECLARE @Byte INT -- byte number
DECLARE @MaxIDPosition INT -- maximum field number
DECLARE @FieldName VARCHAR(128) -- name of changed field
DECLARE @InsertCommand VARCHAR(1024) -- command for inserting in audit
DECLARE @ChangedBy VARCHAR(128)
DECLARE @ChangedOn VARCHAR(20)
SET @ChangedBy = substring(suser_sname(),charindex(''\'',suser_sname())+(1),len(suser_sname()))
SET @ChangedOn = CONVERT(VARCHAR(20),GetDate()) -- tag all the changes consistently
-- Table holding field names and ColumnID
DECLARE @Fields TABLE(ColumnID INT, ColumnName VARCHAR(128))
INSERT INTO @Fields(ColumnID,ColumnName)
SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ''.'' + TABLE_NAME),
COLUMN_NAME, ''ColumnID'') As ColumnID, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '''+ @TableName +''' AND TABLE_SCHEMA = ''' + @SchemaName + '''


SELECT @MaxIDPosition = MAX(ColumnID) FROM @Fields
-- Because we can not execute dynamic sql against INSERTED and DELETED
-- we need a copy of these tables
SELECT * INTO #Deleted FROM deleted
SELECT * INTO #Inserted FROM inserted

SET @IDPosition = 1

WHILE @IDPosition <= @MaxIDPosition
BEGIN
  SELECT @Bit = (@IDPosition-1) % 8 + 1 -- gives 1,2,3,4,5,6,7,8
  SELECT @Bitmask = POWER(2,@Bit - 1) -- gives 1,2,4,8,16,32,64,128
  SELECT @Byte = ((@IDPosition - 1) / 8) + 1 -- gives 1 for for 1-8, 2 for 9 -16 etc.

  IF SUBSTRING(COLUMNS_UPDATED(),@Byte,1) & @Bitmask > 0
  BEGIN
    -- column has changed
    -- Get the fieldname
    SELECT @Fieldname = ColumnName FROM @Fields WHERE ColumnID = @IDPosition
    -- Post the before and after column values
    -- Our timestamp columns are all named TS_ and we want to exclude them
    IF LEFT(@FieldName,3) <> ''TS_''
    BEGIN       SET @InsertCommand = ''INSERT INTO tblAudit(SchemaName, TableName,' + @KeyListInsert + ',
        FieldName, FieldValue, ImageType, ActionType, ChangedBy, ChangedOn)
        SELECT ''''' + @SchemaName +''''', ''''' + @TableName +''''',' + @KeyList + ', '''''' +
        @Fieldname + '''''', CONVERT(VARCHAR(MAX),['' + @FieldName + '']), ''''B'''', ''''U'''', '''''' +
        @ChangedBy + '''''', '''''' + @ChangedOn + '''''' FROM #deleted ''
      EXEC (@InsertCommand)
      SET @InsertCommand = ''INSERT INTO tblAudit(SchemaName, TableName,' + @KeyListInsert + ',
        FieldName, FieldValue, ImageType, ActionType, ChangedBy, ChangedOn)
        SELECT ''''' + @SchemaName + ''''', ''''' + @TableName +''''',' + @KeyList + ', '''''' +
        @Fieldname + '''''', CONVERT(VARCHAR(MAX),['' + @FieldName + '']), ''''A'''', ''''U'''', '''''' +
        @ChangedBy+ '''''', '''''' + @ChangedOn + '''''' FROM #inserted ''
      EXEC (@InsertCommand)
    END
  END
  SET @IDPosition = @IDPosition + 1
END'
  EXEC (@cmd)
  -- useful if debugging comment out the exec
  --print @cmd
  --goto endall
SkipTheTable:
  FETCH NEXT FROM Table_Cursor INTO @SchemaName , @TableName
END
endall:
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO

Download Code

The sample TSQL can be downloaded from here.

Other good resources for alternative strategies are as follows:-


SQL Server Built-In Auditing

If you want to learn more about the built in auditing capabilities of SQL Server, then follow this link http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.



Top of Page