2

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:

enter image description here

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?

0
3

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%';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.