Do you know the last time a Server Property was changed on your instances of SQL Server?
Are you wondering when the setting for max degree of parallelism was changed?
Do you know who changed the setting?
In some environments there are a lot of hands trying to help mix the pot. Sometimes more hands can make light work. This is not always the case though. More hands in the mix can be a troublesome thing. Especially when things start changing and the finger pointing starts but nobody really knows for sure who made the change or when the change was made.
I know, that is why there is this concept called change management. If you make a change to a setting, it should be documented, approved and communicated. Unfortunately the process does not always dictate the work flow and it may be circumvented. This is why auditing is a tool that is in place and should be in place – like it or not.
Auditing can be a very good tool. Like many things – too much of a good thing is not a good thing. Too much auditing can be more of a hindrance than help. You don’t want to cause interference by auditing too many things. You also don’t want too much data that the facts get blurred. I hope that this script strikes more of a balance with just the right amount of data being captured to be of use.
The basic principle to auditing server configs is to find what values changes, when they were changed and by whom. There are ancillary details that can be helpful in tracking the source of the change such as the hostname of the source computer making the change. These are all things that we should capture. But if a setting hasn’t changed – then we need not necessarily report that the setting was unchanged (it should go without saying).
So for this, I created a couple of tables and then a script that I can add to a job to run on a regular basis. I can put the script in a stored procedure should I desire. I’ll leave that to you as an exercise to perform.
[codesyntax lang=”tsql”]
USE AdminDB;
GO
SET NOCOUNT ON;
DECLARE @ConfigLastUpdateDateDATETIME
,@PreviousPollDateDATETIME
,@MaxPollDateDATETIME
,@pathNVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
CREATE TABLE #DBCCConfig(
[ParentObject] VARCHAR (100),
[Object] VARCHAR (100),
[Field] VARCHAR (100),
[Value] VARCHAR (100));
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM cte t1
CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
IF NOT EXISTS (SELECT Name
FROM sys.objects
WHERE name = 'SysConfigAudit')
CREATE TABLE SysConfigAudit (
configuration_idint
,nameNVARCHAR(256)
,valuesql_variant
,minimumsql_variant
,maximumsql_variant
,value_in_usesql_variant
,descriptionNVARCHAR(MAX)
,is_dynamicbit
,is_advancedBIT
,PollDateDATE
,LastConfigUpdtDateDATETIME)
IF NOT EXISTS (SELECT Name
FROM sys.objects
WHERE name = 'SysConfigChangeLog')
CREATE TABLE SysConfigChangeLog (
configuration_idint
,nameNVARCHAR(256)
,CurrValueSQL_VARIANT
,PrevValueSQL_VARIANT
,descriptionNVARCHAR(MAX)
,PollDateDATE
,LastConfigUpdtDateDATETIME
,PrevConfigUpdtDateDATETIME
,ChangeDateDATETIME
,ChangeByNVARCHAR(256)
,HostNameNVARCHAR(256)
,ChangeSPIDSQL_VARIANT
,ChangedataNVARCHAR(2000)
,ApplicationName NVARCHAR(256)
,Severity INT
,ERROR SQL_VARIANT
,ChangeBySessionLoginNVARCHAR(256))
IF NOT EXISTS (SELECT Name
FROM sys.key_constraints
WHERE name ='PK_SysConfigChangeLog'
AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
PK_SysConfigChangeLog PRIMARY KEY CLUSTERED
(
configuration_id,
ChangeDate DESC
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON Admin_Data
END
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
FROM dbo.SysConfigAudit
ORDER BY PollDate DESC),'1/1/1900')
/* Write the configurations out to audit table
*/IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
INSERT INTO SysConfigAudit
( configuration_id ,
name ,
value ,
minimum ,
maximum ,
value_in_use ,
description ,
is_dynamic ,
is_advanced,
PollDate,
LastConfigUpdtDate
)
SELECT configuration_id,name
,value
,minimum,maximum
,value_in_use
,description
,is_dynamic,is_advanced
,GETDATE(),@ConfigLastUpdateDate
FROM master.sys.configurations;
END
/* Recast MaxPollDate */SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
FROM dbo.SysConfigAudit
ORDER BY PollDate DESC),'1/1/1900')
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate
FROM dbo.SysConfigAudit
WHERE Polldate <> @MaxPollDate
ORDER BY PollDate DESC),'1/1/1900');
/*A configuration has changed and a reboot has occurred
causing the updtdate to be written to the config block of the page 10
The configuration may not be written to the page, but we will write it to
the table anyway and then compare to the default trace file in all cases.
If there are any values changed, then write those to the change log with
the login of the person who changed the value
*/BEGIN
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
,df.SessionLoginName,df.ApplicationName
,SUBSTRING(df.TextData
,CHARINDEX('''',df.TextData)+1
,CHARINDEX('''',df.TextData
,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
AS ConfigName
,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
,CHARINDEX('''',df.TextData)+1
,CHARINDEX('''',df.TextData
,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
ORDER BY df.StartTime DESC) AS RowNum
FROM ::fn_trace_gettable( @path, DEFAULT ) df
WHERE 1=1
AND df.TextData LIKE '%Configuration option%'
AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
AND df.Severity IS NOT NULL
)
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate
,LastConfigUpdtDate,PrevConfigUpdtDate,ChangeDate,ChangeBy
,HostName,ChangeSPID,Changedata,ApplicationName,Severity
,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
,df.SessionLoginName AS ChangeBySessionLogin
FROM AdminDB.dbo.SysConfigAudit CUR
INNER JOIN AdminDB.dbo.SysConfigAudit Prev
ON CUR.configuration_id = Prev.configuration_id
AND CUR.PollDate = @MaxPollDate
AND Prev.PollDate = @PreviousPollDate
CROSS APPLY presel df
WHERE df.RowNum = 1
AND df.ConfigName = CUR.NAME
AND CUR.VALUE <> Prev.VALUE
AND NOT EXISTS (SELECT configuration_id
FROM SysConfigChangeLog
WHERE Name = Cur.NAME
AND CurrValue = CUR.VALUE
AND PrevValue = Prev.VALUE
AND ChangeDate = df.StartTime
AND ChangeBy = df.LoginName)
;
END
SELECT *
FROM dbo.SysConfigChangeLog
ORDER BY ChangeDate DESC;[/codesyntax]
Here I am trapping the config settings on a daily basis (as the script is written for now). I then cross reference the current settings against the previous settings. Then I check the default trace to see if any of those settings exist in the trace file.
The default trace captures the data related to configuration changes. On busy systems, it is still possible for these settings to roll out of the trace files. For those systems, we may need to make some alterations to the script (such as running more frequently and changing the comparisons to account for smaller time intervals than a day break).
To ensure proper comparison between the audit tables and the trace file, note the substring function employed. I can capture the configuration name and then join to the audit tables on configuration name.
This has proven useful to me so far in tracking who did what to which setting and when they did it.
I hope you will find good use for it as well.
