Add User Audit To Configuration Change Log(s)

[jacobgexigo]

I wonder if there is a good way to be able to capture the login making a change to the configuration layers. Had a customer ask when xp_cmdshell was enabled on their instance and by who...I was able to tell them when by looking at the Configuration Change Log grid, but not the who. I believe we should be able to pull this out of the default trace if no other good option exists.

[DavidWiseman]

This wouldn't be captured in the default trace - I don't think there would be a way to work out who made the change in this instance. I'm aware of a couple of ways to capture this information: event notifications and server-level DDL triggers. Extended events can probably also capture it.

I created an app a while ago to capture these changes with event notifications. I stopped developing it though due to incompatibility between event notifications and in-memory OLTP. I think DDL triggers have similar issues with in-memory OLTP.
It's still available here but I wouldn't recommend using it as I'm unlikely to update it.

[jacobgexigo]

As long as you have access to it, you should be able to get it in the default trace.

DECLARE @trc_path VARCHAR(500)
SELECT @trc_path = CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT) WHERE property=2

SELECT TEXTData, HostName, ApplicationName, DatabaseName, LoginName, SPID, StartTime, EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%configure%' AND SPID<>@@spid
ORDER BY StartTime DESC

[DavidWiseman]

Yeah, it looks like it's available in the default trace as you described. I was getting mixed up with the system_health session. Good to know it's captured in the default trace.