Use trigger on DELETE to retrieve original SQL command and user who did it

auditdeletesql serversql-server-2012trigger

I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012.

What I need is the username that executed it and the command executed, then I can insert them on a log table.

I'm finding many example for creating trigger, but none that shows how to retrieve these info.

Best Answer

For the username, there are a bunch of built-in functions that will return the current user name. They are not all the same, so take your pick - I typically use SUSER_SNAME().

For the command, if you were on a supported and non-antique version (even SQL Server 2014 would do), you could use sys.dm_exec_input_buffer to tell you the last batch sent by the current user (identified by @@SPID), so in the body of your trigger you could have:

INSERT dbo.LoggingTable(username, command<, other cols>)
SELECT SUSER_SNAME(), event_info<, other data>
  FROM sys.dm_exec_input_buffer(@@SPID, NULL);

On SQL Server 2012, it's going to be a lot uglier. You'll need a #temp table to hold the output of dynamic SQL executing DBCC INPUTBUFFER:

DECLARE @sql nvarchar(max) = N'DBCC INPUTBUFFER(@me) WITH NO_INFOMSGS;';

CREATE TABLE #dbcc
(
  EventType  nvarchar(128),
  Parameters int,
  EventInfo  nvarchar(max),
  UserName   nvarchar(128) NOT NULL DEFAULT SUSER_SNAME()
);

INSERT #dbcc(EventType, Parameters, EventInfo)
  EXEC sys.sp_executesql @sql, N'@me int', @@SPID;

INSERT dbo.LoggingTable(username, command<, other cols>)
SELECT UserName, EventInfo<, other data>
  FROM #dbcc;
Related Question