Capture configuration changes....

Last Post 30 Jan 2011 12:43 PM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
New Member
New Member

28 Jan 2011 11:23 AM
I wanted to know some suggestions (no triggers, and rather not store data), to capture value changes to the configuration values in sys.sysconfigures table (sp_configure).  So, if say someone turns xp_cmdshell on, it will change from 0 to 1.  Is there a way 2005-2008 preferably to do this?  (such as an Xevent or other method)?  I'm open to any suggestion.  I know CDC can do it in 2008, but have some 2005 servers as well.  TIA
New Member
New Member

28 Jan 2011 12:05 PM
No such feature comes with sql2k5.
New Member
New Member

30 Jan 2011 05:55 AM
You can capture that in a trace.
New Member
New Member

30 Jan 2011 12:43 PM
The default trace already captures most if not all of DDL type changes and you can see the events and columns it uses by running the query directly below. The query at the bottom can be used to find the entries with configuration changes. Just change the name of the trace file to the first one in that folder that matches yours. You can find the folder by looking at sys.traces.

SELECT a.[EventID], b.[name] AS [Even Name], a.[ColumnID],
c.[name] AS [Column Name], d.[name] AS [Category]
FROM fn_trace_geteventinfo(1) AS a INNER JOIN sys.trace_events AS b
ON a.EventID = b.Trace_Event_ID
INNER JOIN sys.trace_columns AS c
ON a.ColumnID = c.Trace_Column_ID
INNER JOIN sys.trace_categories AS d
ON b.Category_ID = d.Category_ID
ORDER BY a.[EventID], a.[ColumnID]

SELECT EventClass, StartTime, *
FROM ::FN_TRACE_GETTABLE('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008DEV\MSSQL\Log\log_113.trc', DEFAULT)
WHERE TextData like '%configure%'

Acceptable Use Policy