SQL 2005 - Sys.configurations

sysconfigures is the table which stores the SQL Server configurations in SQL 2000.The same is available as view in SQL 2005 as sys.Configurations.

However to update the same we use the same procedure sp_configure as we used in SQL 2000.

There are some new configurations which are added to SQL 2005,lets us what new has been added considering that we already know what configurations are available in SQL 2000

server trigger recursion - To Allow recursion for server level triggers
clr enabled - CLR user code execution enabled in the server
remote admin connections - Dedicated Admin Connections are allowed from remote clients

So now we have 14 configurations in SQL 2005 when compared to 11 in SQL 2000.

And with Advanced Options enabled we have 62 configurations as compared to 37 in SQL2000.

Some interesting new options are xp_cmdshell - Enable or disable command shell which is very useful and makes the SQL more secured.

And separate setting for each Extended stored procedures like Agent XPs,SQL Mail XPs,Database Mail XPs,SMO and DMO XPs.

So to view the configuration options you can use same as sp_configure or use the blow query to view the same.


select * from sys.configurations
where is_advanced column shows whether it is advanced option or not and also is_dynamic suggests whether reconfigure with override and restart of SQL is required for settings to take effect