Brian I have this saved in my snippets as the solution for getting most of the command(DBCC inputbuffer is limited to 256 chars)

this worked at the end of a long thread I participated in on the same subject: this thread was trying to find out who/what process was setting a column value to null, which the OP figured to do in a trigger.

as far as the sysadmin thing, just have your trigger use EXECUTE AS to solve the sysadmin issue.

CREATE TABLE WHATEVER(

WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

DESCRIP VARCHAR(30)

)

INSERT INTO WHATEVER(DESCRIP)

SELECT 'APPLES' UNION

SELECT 'ORANGES' UNION

SELECT 'BANANAS' UNION

SELECT 'GRAPES' UNION

SELECT 'CHERRIES' UNION

SELECT 'KIWI'

--used to capture the row id plus a bunch of audit information

CREATE TABLE [dbo].[WHATEVER_AUDIT] (

[WHATEVERID] INT NOT NULL,

[INSERTUPDATE] NVARCHAR(30) NULL,

[LASTCOMMAND] NVARCHAR(max) NULL,

[USER_NAME] NVARCHAR(256) NULL,

[SUSER_NAME] NVARCHAR(256) NULL,

[CURRENT_USER] NVARCHAR(256) NULL,

[SYSTEM_USER] NVARCHAR(256) NULL,

[SESSION_USER] NVARCHAR(256) NULL,

NVARCHAR(256) NULL,

[APPLICATION_NAME] NVARCHAR(256) NULL,

[HOST_NAME] NVARCHAR(256) NULL,

[OCCURANCE_DATE] DATETIME DEFAULT GETDATE() NOT NULL)

GO

CREATE TRIGGER TR_WHATEVER

ON WHATEVER

FOR INSERT,UPDATE

AS

BEGIN

DECLARE @INSERTUPDATE NVARCHAR(30),

@LASTCOMMAND NVARCHAR(max)

--################################################################################################

--note these two methods do not get the last command when inside a trigger;

--included for complete solution

--get the last command by the current spid:

--DECLARE @handle varbinary(64)

--SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID

--SELECT @LASTCOMMAND = [Text] FROM ::fn_get_sql(@Handle)

--get the last command by the current spid:

--SELECT @LASTCOMMAND = DEST.TEXT

--FROM sys.[dm_exec_connections] SDEC

--CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST

--WHERE SDEC.[most_recent_session_id] = @@SPID

--################################################################################################

--because dbcc inputbuffer is limited to 256 chars, you may need to combine this witha DML trace

--################################################################################################

DECLARE @SQLBuffer nvarchar(4000)

DECLARE @buffer TABLE (

EventType nvarchar(30),

Parameters int,

EventInfo nvarchar(4000)

)

INSERT @buffer

EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

SELECT @LASTCOMMAND = EventInfo

FROM @buffer

--assume it is an insert

SET @INSERTUPDATE='INSERT'

--if there's data in deleted, it's an update

IF EXISTS(SELECT * FROM DELETED)

SET @INSERTUPDATE='UPDATE'

--insert data that meets the criteria: the column 'description' is null

INSERT INTO [WHATEVER_AUDIT]

SELECT

INSERTED.WHATEVERID,

@INSERTUPDATE,

@LASTCOMMAND,

user_name() AS [user_name],

suser_name() AS [suser_name],

current_user AS [current_user],

system_user AS [system_user],

session_user AS [session_user],

user AS ,

APP_NAME() AS [application_name],

HOST_NAME() AS [host_name],

getdate() AS [occurance_date]

FROM INSERTED

WHERE DESCRIP IS NULL

END --TRIGGER

GO

--does not trigger audit:

INSERT INTO WHATEVER(DESCRIP)

SELECT 'CANTALOUPE' UNION

SELECT 'TANGARINES' UNION

SELECT 'PLUMS' UNION

SELECT 'PEACHES' UNION

SELECT 'BLUEBERRIES'

--triggers one row out of multi row insert

INSERT INTO WHATEVER(DESCRIP)

SELECT NULL UNION

SELECT 'TANGARINES'

--triggers one row out of multi row insert

UPDATE WHATEVER SET DESCRIP = NULL WHERE WHATEVERID IN (4,5)

SELECT * FROM WHATEVER

SELECT * FROM [WHATEVER_AUDIT]