SQL SERVER: Non Default Server Settings

NonDefaultServerSettings.sql

/*=============================================
	  File: SQL_Server_config_check.sql
 
	  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
	  http://thomaslarock.com/2014/08/sql-server-configuration-check/
 
	  Summary: This script will check the values of your sys.configurations table
	   and compare it to the default values. The script should return a row for any 
	   configuration option that is currently set to a non-default value. 
 
	   I’ve created one table variable to hold the default values for every version of 
	   SQL Server going back to SQL 2005. You can verify for yourself that I have used
	   the default values for each version by reading this BOL entry:
 
	   http://msdn.microsoft.com/en-us/library/ms189631.aspx
 
	   You’ll note that the default values have not changed(!) between versions, but 
	   certain configuration options are not available in each version. By using an 
	   inner join on the name column I believe the extra rows are not an issue for 
	   our final result set.
 
	   But hey, I’ve been wrong before.
 
	  Date: July 24th, 2014
 
	  SQL Server Versions: SQL 2005, SQL 2008, SQL 2008R2, SQL 2012, SQL 2014
 
	  You may alter this code for your own purposes. You may republish
	  altered code as long as you give due credit. 
 
	  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
	  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
	  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
	  FITNESS FOR A PARTICULAR PURPOSE.
 
	=============================================*/

	with cte_config as (
		select [name] = 'access check cache bucket count', [default_value] = 0 union 
		select [name] = 'access check cache quota', [default_value] = 0 union
		select [name] = 'Ad Hoc Distributed Queries', [default_value] = 0 union
		select [name] = 'affinity I/O mask', [default_value] = 0 union 
		select [name] = 'affinity64 I/O mask', [default_value] = 0 union 
		select [name] = 'affinity mask', [default_value] = 0 union 
		select [name] = 'affinity64 mask', [default_value] = 0 union 
		select [name] = 'Agent XPs', [default_value] = 1 union  -- Changes to 1 if SQL Agent is started, so I check for that
		select [name] = 'allow updates', [default_value] = 0 union 
		select [name] = 'awe enabled', [default_value] = 0 union 
		select [name] = 'backup compression default', [default_value] = 0 union 
		select [name] = 'blocked process threshold (s)', [default_value] = 0 union 
		select [name] = 'c2 audit mode', [default_value] = 0 union 
		select [name] = 'clr enabled', [default_value] = 0 union 
		select [name] = 'common criteria compliance enabled', [default_value] = 0 union 
		select [name] = 'contained database authentication', [default_value] =  0 union  
		select [name] = 'cost threshold for parallelism', [default_value] = 5 union 
		select [name] = 'cross db ownership chaining', [default_value] = 0 union 
		select [name] = 'cursor threshold', [default_value] = -1 union 
		select [name] = 'Database Mail XPs', [default_value] = 0 union 
		select [name] = 'default full-text language', [default_value] = 1033 union 
		select [name] = 'default language', [default_value] = 0 union 
		select [name] = 'default trace enabled', [default_value] = 1 union 
		select [name] = 'disallow results from triggers', [default_value] = 0 union 
		select [name] = 'EKM provider enabled', [default_value] = 0 union 
		select [name] = 'filestream access level', [default_value] = 0 union 
		select [name] = 'fill factor (%)', [default_value] = 0 union 
		select [name] = 'ft crawl bandwidth (max)', [default_value] = 100 union 
		select [name] = 'ft crawl bandwidth (min)', [default_value] = 0 union 
		select [name] = 'ft notify bandwidth (max)', [default_value] = 100 union 
		select [name] = 'ft notify bandwidth (min)', [default_value] = 0 union 
		select [name] = 'index create memory (KB)', [default_value] = 0 union 
		select [name] = 'in-doubt xact resolution', [default_value] = 0 union 
		select [name] = 'lightweight pooling', [default_value] = 0 union 
		select [name] = 'locks', [default_value] = 0 union 
		select [name] = 'max degree of parallelism', [default_value] = 0 union 
		select [name] = 'max full-text crawl range', [default_value] = 4 union 
		select [name] = 'max server memory (MB)', [default_value] = 2147483647 union 
		select [name] = 'max text repl size (B)', [default_value] = 65536 union 
		select [name] = 'max worker threads', [default_value] = 0 union 
		select [name] = 'media retention', [default_value] = 0 union 
		select [name] = 'min memory per query (KB)', [default_value] = 1024 union 
		select [name] = 'min server memory (MB)', [default_value] = 0 union 
		select [name] = 'nested triggers', [default_value] = 1 union 
		select [name] = 'network packet size (B)', [default_value] = 4096 union 
		select [name] = 'Ole Automation Procedures', [default_value] = 0 union 
		select [name] = 'open objects', [default_value] = 0 union 
		select [name] = 'optimize for ad hoc workloads', [default_value] = 0 union 
		select [name] = 'PH timeout (s)', [default_value] = 60 union 
		select [name] = 'precompute rank', [default_value] = 0 union 
		select [name] = 'priority boost', [default_value] = 0 union 
		select [name] = 'query governor cost limit', [default_value] = 0 union 
		select [name] = 'query wait (s)', [default_value] = -1 union 
		select [name] = 'recovery interval (min)', [default_value] = 0 union 
		select [name] = 'remote access', [default_value] = 1 union 
		select [name] = 'remote admin connections', [default_value] = 0 union 
		select [name] = 'remote login timeout (s)', [default_value] = 10 union 
		select [name] = 'remote proc trans', [default_value] = 0 union 
		select [name] = 'remote query timeout (s)', [default_value] = 600 union 
		select [name] = 'Replication XPs', [default_value] = 0 union 
		select [name] = 'scan for startup procs', [default_value] = 0 union 
		select [name] = 'server trigger recursion', [default_value] = 1 union 
		select [name] = 'set working set size', [default_value] = 0 union 
		select [name] = 'show advanced options', [default_value] = 0 union 
		select [name] = 'SMO and DMO XPs', [default_value] = 1 union 
		select [name] = 'SQL Mail XPs', [default_value] = 0 union 
		select [name] = 'transform noise words', [default_value] = 0 union 
		select [name] = 'two digit year cutoff', [default_value] = 2049 union 
		select [name] = 'user connections', [default_value] = 0 union 
		select [name] = 'user options', [default_value] = 0 union 
		select [name] = 'Web Assistant Procedures', [default_value] =  0 union 
		select [name] = 'xp_cmdshell', [default_value] = 0
	)

	SELECT [sc].[name],
		   [sc].value,
		   [sc].[value_in_use],
		   [c].[default_value]
	FROM   [sys].[configurations] [sc]
		   INNER JOIN cte_config [c] ON [sc].[name] = [c].[name]
	WHERE  [sc].value <> [sc].[value_in_use]
		   OR [sc].[value_in_use] <> [c].[default_value];