Auditing a complex mix of reasonably small tables

Question

I plan to use the following approach to audit the tables that represent user-editable configuration of an automated system (over the course of system's life these will be inevitably extended in their numbers and stuffed with more columns). This approach will not be applied to main data tables as those are automatically updated.

SQL Server 2005+, based on this. Suggestions?

CREATE TABLE [dbo].[audit] (
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [time] [datetime] NOT NULL,
      [username] [nvarchar](128) NOT NULL,
      [useraddress] [nvarchar](48) NULL,
      [tablename] [nvarchar](128) NULL,
      [operation]  AS (case when [oldvalue] IS NULL then 'I' when [newvalue] IS NULL then 'D' else 'U' end),
      [oldvalue] [xml] NULL,
      [newvalue] [xml] NULL
)
GO

ALTER TABLE [dbo].[audit] ADD CONSTRAINT [DF_audit_time] DEFAULT (sysutcdatetime()) FOR [time]
GO

CREATE PROCEDURE [dbo].[save_audit] 
      @old_values xml,
      @new_values xml,
      @tablename nvarchar(128)
AS
BEGIN
      SET NOCOUNT ON;

      IF @old_values IS NOT NULL OR @new_values IS NOT NULL
      BEGIN
            DECLARE
                  @username nvarchar(128),
                  @useraddress varchar(48)

            SELECT @username = original_login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
            SELECT @useraddress = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID

            INSERT INTO audit (username, useraddress, tablename, oldvalue, newvalue)
            VALUES (@username, @useraddress, @tablename, @old_values, @new_values)
      END
END
GO

CREATE TRIGGER [dbo].[audit_tablename] ON [dbo].[tablename] 
    AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @i xml, @d xml, @t nvarchar(128)
    SET @i = (SELECT * FROM inserted FOR XML AUTO)
    SET @d = (SELECT * FROM deleted FOR XML AUTO)
    SET @t = (SELECT OBJECT_NAME(parent_object_id) FROM sys.objects WHERE object_id = @@PROCID)
    EXEC save_audit @d, @i, @t
END

EDIT: Table name detection was found unreliable on 2005, fixed.

EDIT2: Not all versions of SQL Server 2005 have original_login_name column in sys.dm_exec_sessions (it was added with SP2, apparently), will have to use login_name if targeting all versions (the meaning is different, though).

Answer 1

Looks Like a nice, straight forward solution.

The only suggestion that I have , and while this is an unlikely situation, if the database were hosted on a failover cluster and the user were to connect to the instance locally (i.e. from anyone of the servers in the cluster), you wouldn't know which computer they logged in from. The value of @useraddress would be set as '<local machine>'.

This would also be the case when a user connects locally to the SQL Server instance, but if there's only one instance (i.e. not in a cluster), then it's not really a big deal, since you know which computer that is.

Although, to play it safe, why not modify the code to set the value for @useraddess like this, which will give you the computer name instead of '<local machine>':

DECLARE @useraddress nvarchar(128); --<== Originally set as varchar(48)

SELECT @useraddress = REPLACE(client_net_address
                     ,'<local machine>'
                     ,CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)))
  FROM sys.dm_exec_connections
 WHERE session_id = @@SPID;

See:

• SERVERPROPERTY (Transact-SQL)