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)GOALTER TABLE [dbo].[audit] ADD CONSTRAINT [DF_audit_time] DEFAULT (sysutcdatetime()) FOR [time]GOCREATE PROCEDURE [dbo].[save_audit]       @old_values xml,      @new_values xml,      @tablename nvarchar(128)ASBEGIN      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)      ENDENDGOCREATE TRIGGER [dbo].[audit_tablename] ON [dbo].[tablename]     AFTER INSERT,DELETE,UPDATEAS 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, @tENDEDIT: 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).
Auditing a complex mix of reasonably small tables
sql;sql server
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)