Meta

Category: 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:CREATETABLE dbo.audit_target

2: (

3: id INTIDENTITYPRIMARYKEY,

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:CREATETABLE dbo.history

2: (

3: id INTIDENTITYPRIMARYKEY,

4: [table_name] NVARCHAR(30) NOTNULL,

5: table_id INTNOTNULL,

6: [changes] NVARCHAR(MAX),

7: [timestamp] SMALLDATETIME NOTNULLDEFAULT GETDATE(),

8: [user] NVARCHAR(30) NOTNULL

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:CREATEPROCEDURE 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 ISNULL) 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:INNERJOIN sys.sysobjects t

22:ON p.parent_obj = t.id

23:WHERE p.id = @procid

24:

25:IF ((@tablename ISNULL) OR (@tablename = N''))

26:BEGIN

27:RAISERROR(N'Could not get table name', 16, 16)

28:ROLLBACKTRAN

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 ISNULL) OR (@idname = ''))

41:BEGIN

42:RAISERROR(N'Could not get id column for the current table', 16, 16)

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:CREATETRIGGER dbo.generic_audit_trigger

2:ON dbo.test -- replace this with other target tables' names

3:AFTERUPDATE

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