Server Config Audits

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 @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@path					NVARCHAR(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_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)

IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(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()) &lt;&gt; @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 &lt;&gt; @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 &lt;&gt; 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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

USE AdminDB;

SET NOCOUNT ON;

DECLARE @ConfigLastUpdateDate DATETIME

,@PreviousPollDate DATETIME

,@MaxPollDate DATETIME

,@path NVARCHAR(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_id int

,name NVARCHAR(256)

,value sql_variant

,minimum sql_variant

,maximum sql_variant

,value_in_use sql_variant

,description NVARCHAR(MAX)

,is_dynamic bit

,is_advanced BIT

,PollDate DATE

,LastConfigUpdtDate DATETIME)

IF NOT EXISTS (SELECT Name

FROM sys.objects

WHERE name = 'SysConfigChangeLog')

CREATE TABLE SysConfigChangeLog (

configuration_id int

,name NVARCHAR(256)

,CurrValue SQL_VARIANT

,PrevValue SQL_VARIANT

,description NVARCHAR(MAX)

,PollDate DATE

,LastConfigUpdtDate DATETIME

,PrevConfigUpdtDate DATETIME

,ChangeDate DATETIME

,ChangeBy NVARCHAR(256)

,HostName NVARCHAR(256)

,ChangeSPID SQL_VARIANT

,Changedata NVARCHAR(2000)

,ApplicationName NVARCHAR(256)

,Severity INT

,ERROR SQL_VARIANT

,ChangeBySessionLogin NVARCHAR(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