is there a way to find out who was spid228 at a given time in the past? 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 &gt; (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?
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%';