Recently I have been doing an audit on our enterprise SQL Servers including production and non-production systems and wanted to check the server level configurations. One of the audit items I specifically wanted to look at was the server level default connection options. Unless there is a strong reason to do so, In general it is recommended NOT to tinker with the server level configuration settings [exceptions apply and ymmv] and just accept the default values.
This information can be retrieved from sp_configure or sys.configurations (2005 & above) by looking at the “user options” property.
EXEC sp_configure 'User Options'
GO
SELECT * FROM sys.configurations WHERE name = 'User Options'
GO
Let’s take a look at the data exposed by the above methods. We need to look at either run_vale from sp_configure or value_in_use from sys.configurations to look at the current value set for this property.
What we get from the above is one value and NOT detailed information for each of those individual properties. Using SSMS to retrieve this information manually on several servers is rather boring & tedious and definitely NOT a good use of my or anyone else’s time. This led me to write a T-SQL script to fetch the details of each one of the properties listed under the default connection options.
IF OBJECT_ID('tempdb..#ConnectionOptions', 'U') IS NOT NULL DROP TABLE #ConnectionOptions
IF OBJECT_ID('tempdb..#UserOptions', 'U') IS NOT NULL DROP TABLE #UserOptions
--Create the table and store the configuration values
CREATE TABLE #ConnectionOptions (property VARCHAR(100), value INT)
INSERT #ConnectionOptions
SELECT 'implicit transactions', 2 UNION ALL
SELECT 'cursor close on commit', 4 UNION ALL
SELECT 'ansi warnings', 8 UNION ALL
SELECT 'ansi padding', 16 UNION ALL
SELECT 'ansi nulls', 32 UNION ALL
SELECT 'arithmetic abort', 64 UNION ALL
SELECT 'arithmetic ignore', 128 UNION ALL
SELECT 'quoted identifier', 256 UNION ALL
SELECT 'no count', 512 UNION ALL
SELECT 'ansi null default on', 1024 UNION ALL
SELECT 'ansi null default off', 2048 UNION ALL
SELECT 'concat null yields null', 4096 UNION ALL
SELECT 'numeric round abort', 8192 UNION ALL
SELECT 'xact_abort on', 16384
--define the table structure
CREATE TABLE #UserOptions ( name VARCHAR(100), minimum INT, maximum INT, config_vale INT, run_value INT)
--Retrieve the data
INSERT #UserOptions
EXEC sp_configure 'User options'
DECLARE @currentvalue INT
SELECT @currentvalue = run_value FROM #UserOptions
SELECT property, CASE WHEN (@currentvalue & value) = value
THEN 'has been SET'
ELSE 'NOT SET'
END CurrentValue
FROM #ConnectionOptions
The above results are easy to read and the above code is self explanatory. It uses bitwise operations on the retrieved value from the sp_configure/sys.configurations value .
Recommeded Reading:
How to use SP_CONFIGURE in SQL Server
T-SQL Bitwise Operations
This is post is really very informative. it actually provide me what im searching for.
Helena,
Glad it is helpful for you.
Your solution can be much simplified by
Replacing table #ConnectionOptions with master.dbo.spt_values and a where
Replacing table #UserOptions with master.sys.configurations and a where
Additionally,global variable @@OPTIONS contains the current connection properties.
SELECT ConnectionOptions.name AS ConnectionSettingName
, CASE WHEN @@OPTIONS & ConnectionOptions.number = ConnectionOptions.number THEN ‘ON’
ELSE ‘OFF’
END AS ConnectionSettingInd
, CASE WHEN configurations.OptionBits & ConnectionOptions.number = ConnectionOptions.number THEN ‘ON’
ELSE ‘OFF’
END AS DefaultSettingInd
FROM master.dbo.spt_values as ConnectionOptions
, (select CAST(value_in_use as integer) as OptionBits
from master.sys.configurations
where name = ‘User Options’
) as configurations
WHERE ConnectionOptions.type = ‘SOP’
AND ConnectionOptions.number > 0
Carl,
That’s wonderful. I knew the values of all the options are set in some place but didn’t know there were in master.dbo.spt_values. Thank you for sharing that and your code definitely looks cleaner this way.