is there a way to find out who was spid228 at a given time in the past?

Question

I have a script that reads the error log so that I can see what has been happening within the last day:

--create the table
CREATE TABLE #Radhe (logdate datetime not null default (getdate()), 
                     processinfo varchar(108) not null default ('Radhe'),
                     the_text varchar(4000))


-- create a non-unique clustered index
CREATE CLUSTERED INDEX IXC_RADHE_RADHE ON #RADHE(logdate desc, processinfo  asc)


-- load the table
INSERT #Radhe EXEC xp_readerrorlog


-- read the data
SELECT * FROM #Radhe with (index(IXC_RADHE_RADHE))
WHERE LOGDATE > (GETDATE() - 1)
  AND processinfo LIKE 'SPID%'
order by logdate desc

when I run this script this morning I see the below:

2016-04-13 11:08:23.640 spid228 Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Is there a way I can find out who/IP of spid228 who did the above?

Answer 1

You can easily extract that information from the default trace:

DECLARE @path nvarchar(4000);

SELECT @path= path
FROM sys.traces
WHERE id = 1;

SELECT TextData, HostName, ApplicationName, LoginName, SPID, StartTime
FROM fn_trace_gettable(@path, DEFAULT)
WHERE TextData LIKE '%xp_cmdshell%';