SQL Server Auditing

Probably the best mechanism for auditing changes to tables in SQL Server is Change Data Capture, which I already covered here. Unfortunately, it only works with the Enterprise editions of SQL Server, something that not everyone has access to.

I once had to implement a similar solution that should work on any edition of SQL Server, which is what I am going to talk about. Its only requirement was that for any changes made to target tables, their old values would be stored in a shared history table, with the indication of the timestamp and user who made the change.

Let’s imagine we have some table, anything will do:

   1: CREATE TABLE dbo.audit_target
   2: (
   3:     id INT IDENTITY PRIMARY KEY,
   4:     a NVARCHAR(50),
   5:     b INT
   6: )

As you can see, it’s just a regular table with a single-column primary key and a couple of other columns, any number and type will do, doesn’t really matter.

Now let’s create a table for holding the change history:

   1: CREATE TABLE dbo.history
   2: (
   3:     id INT IDENTITY PRIMARY KEY,
   4:     [table_name] NVARCHAR(30) NOT NULL,
   5:     table_id INT NOT NULL,
   6:     [changes] NVARCHAR(MAX),
   7:     [timestamp] SMALLDATETIME NOT NULL DEFAULT GETDATE(),
   8:     [user] NVARCHAR(30) NOT NULL
   9: )

The table_name column shall hold the name of the table whose changes we are tracking, table_id, the id of the updated row, changes will get a XML containing all the old values, timestamp the date and time the change occurred, and user, the person who made the change (more on this later).

OK, next we need a stored procedure to populate the changes table:

   1: CREATE PROCEDURE dbo.generic_audit_procedure
   2: (
   3:     @procid INT,
   4:     @xml XML
   5: )
   6: AS
   7: BEGIN
   8:     SET NOCOUNT ON
   9:     
  10:     -- check if there are changes, in which case, xml will not be empty
  11:     IF ((@xml IS NULL) OR (CAST(@xml AS NVARCHAR(MAX)) = N''))
  12:     BEGIN
  13:         RETURN 0
  14:     END
  15:     
  16:     -- get the current table id for the current process (trigger)
  17:     DECLARE @tablename NVARCHAR(30)
  18:  
  19:     SELECT @tablename = OBJECT_NAME(t.id)
  20:     FROM sys.sysobjects p
  21:     INNER JOIN sys.sysobjects t
  22:     ON p.parent_obj = t.id 
  23:     WHERE p.id = @procid
  24:     
  25:     IF ((@tablename IS NULL) OR (@tablename = N''))
  26:     BEGIN
  27:         RAISERROR(N'Could not get table name', 16, 16)
  28:         ROLLBACK TRAN
  29:         RETURN
  30:     END
  31:     
  32:     -- get the primary key column for the current table
  33:     DECLARE @idname NVARCHAR(30)
  34:  
  35:     SELECT @idname = name 
  36:     FROM sys.syscolumns
  37:     WHERE id = OBJECT_ID(@tablename)
  38:     AND colstat = 1
  39:  
  40:     IF ((@idname IS NULL) OR (@idname = ''))
  41:     BEGIN
  42:         RAISERROR(N'Could not get id column for the current table', 16, 16)
  43:         ROLLBACK TRAN
  44:         RETURN
  45:     END
  46:     
  47:     -- get the current user from the context
  48:     DECLARE @username NVARCHAR(30)
  49:  
  50:     SELECT @username = CONVERT(NVARCHAR(30), CONTEXT_INFO())
  51:  
  52:     IF ((@username IS NULL) OR (@username = ''))
  53:     BEGIN
  54:         RAISERROR(N'Could not get current username', 16, 16)
  55:         ROLLBACK TRAN
  56:         RETURN
  57:     END
  58:  
  59:     DECLARE @tmp TABLE([key] NVARCHAR(MAX), [value] XML)
  60:  
  61:     INSERT INTO @tmp SELECT q.[key], q.[value]
  62:     FROM
  63:     (
  64:         SELECT T.N.value(N'(*[local-name(.)=sql:variable("@idname")])[1]', N'INT') AS [key], T.N.query(N'*') AS [value]
  65:         FROM @xml.nodes('/row') AS T(N)    
  66:     ) q
  67:  
  68:     DECLARE @ROWS INT
  69:     SET @ROWS = 0
  70:  
  71:     -- loop all modified records
  72:     WHILE (1 = 1)
  73:     BEGIN
  74:         DECLARE @id NVARCHAR(MAX)
  75:         DECLARE @value XML
  76:         
  77:         -- pick the first record
  78:         SELECT TOP 1 @id = [key], @value = [value]
  79:         FROM @tmp
  80:         
  81:         IF (@@ROWCOUNT = 0)
  82:         BEGIN
  83:             BREAK
  84:         END
  85:                         
  86:         -- insert into the shared table
  87:         INSERT INTO dbo.history (table_name, table_id, [user], [changes])
  88:         VALUES (@tablename, @id, @username, CAST(@value AS NVARCHAR(MAX)))
  89:         
  90:         -- increment the updated rows
  91:         SET @ROWS = @ROWS + @@ROWCOUNT
  92:         
  93:         -- remove the processed record
  94:         DELETE FROM @tmp
  95:         WHERE [key] = @id
  96:     END
  97:  
  98:     RETURN @ROWS
  99: END

The generic_audit_procedure will get the current table from the current process id, as passed in the @@procid parameter, the current user from CONTEXT_INFO (again, more on this later), the changes from the @xml parameter and will insert everything into the history table.

And the final part, a trigger to “glue” together the changes made to a table to the stored procedure:

   1: CREATE TRIGGER dbo.generic_audit_trigger
   2: ON dbo.test -- replace this with other target tables' names
   3: AFTER UPDATE
   4: AS
   5: BEGIN
   6:     SET NOCOUNT ON
   7:     
   8:     -- get all changes
   9:     -- this needs to be done on the trigger
  10:     DECLARE @xml XML
  11:     SET @xml = (SELECT * FROM inserted FOR XML PATH('row'))
  12:     
  13:     -- call the generic stored procedure
  14:     EXEC dbo.generic_audit_procedure @@PROCID, @xml
  15: END

This trigger will run after changes are accepted (AFTER UPDATE) and will cast all changes (the INSERTED pseudo-table) as XML, and then invoke the generic_audit_procedure stored procedure with it as an argument, together with the current process id (@@PROCID). Of course, you need to call this for every table that you wish to audit.

The only thing left is, we need to provide a way for SQL Server to know the name of the current user. We do that by manually executing the following query (actually, it was an automated process that was executed by the data access library):

   1: DECLARE @username VARBINARY(128) = CAST(N'rjperes' AS VARBINARY(128))
   2: SET CONTEXT_INFO @username

So, whenever a change is made to a table, it will appear as this:

image

You can see that the changes column will contain an XML node with all the old values that were changed by the update made to the target table.

In a nutshell:

  1. We add a trigger that calls a stored procedure upon change to a number of target tables;
  2. Save the application user in CONTEXT_INFO;
  3. Modify the data in a table;
  4. Get the old values in an auditing table;


There are some limitations with this implementation:

  • Only single-column primary key target tables are supported;
  • All of the “old” values are recorded, not just those that changed;
  • It only keeps the “old” values, not the new ones, although this is by design and easy to change;
  • Binary columns could use a better treatment;
  • Doesn’t keep track of deleted or inserted records;
  • Doesn’t track schema changes (add/drop/modify columns, etc);
  • It is necessary to set CONTEXT_INFO explicitly with the application user that we want to record;
  • Although I didn’t implement it, it would be easy to add a version column to the history table, to keep track of how many changes were made to each target table.

However, it suited perfectly what I needed, maybe it will work for you too! Winking smile








                             

No Comments

Add a Comment

As it will appear on the website

Not displayed

Your website