Changing configuration settings with sp_configure
5September 22, 2014 by Kenneth Fisher
I was recently asked where to go in Configuration Manager to change the settings for xp_cmdshell. It was then that I realized that the existence of sp_configure is probably not universal knowledge. I was truly shocked. For some reason I had thought that DBA’s were born with this knowledge. As this turns out not to be the case, I felt it would be a good idea to give sp_configure a brief run through.
Quick disclaimer: This stored procedure modifies system settings. Be sure you understand the setting you are changing and the need for the change before making it.
The system stored procedure sp_configure is used to view or modify a number of different system settings. These settings include the default MAXDOP for the server, whether SMOs is enabled, whether xp_cmdshell is enabled and a long list of others. To get a complete list you can either query sys.configurations or run sp_configure with no parameters.
At this point you might have a couple of questions:
- Why is the list from sp_configure shorter than the list from sys.configurations?
- If I can enable xp_cmdshell using sp_configure, why isn’t that setting showing up?
The answer to both of these is the same. Some configurations are considered Advanced. If you want to see them you will need to change the setting show advanced options (using of course sp_configure).
EXEC sp_configure 'show advanced options', 1 RECONFIGURE
“But wait,” you’re wondering “Why are you are adding in this RECONFIGURE command?”
Because sp_configure only changes the configured value. RECONFIGURE updates the running value with the configured value (there are a few exceptions where a reboot or instance restart is required). So in order to actually change the currently running configuration value you have to use both commands. Fortunately RECONFIGURE checks that the configured value is valid before setting it as the running value. If you are absolutely certain you know what you are doing and need to update one of the configuration values with something outside of its allowed range you can include the WITH OVERRIDE option of the RECONFIGURE command.
So here’s the thing, if you are going to be administrating SQL Server you need to have at least a passing familiarity of the sp_configure and RECONFIGURE commands and a reasonable idea of what settings they modify.
SQL Studies 
One feature of sp_configure is that you only need to type enough of the configuration name fore it to be recognizable. For example, sp_configure ‘show’, 1 is sufficient for ‘show advanced’ (try ‘sh’ :)).
If someone insists upon using the SSMS GUI (to enable xp_cmdshell), right click the sever name, select Facets and explore the options. MS’ decision to use the word ‘facet’ was a poor one, IMO (and that is why I remember the name :)). My guess is that the word ‘configure’ has been over-used since 4.0 :).
That’s really cool. I’ve never tried that before. I’ll have to explore that more closely.
This rules. It’s a great help to use for quick reference and (almost) idiot proof this.
[…] Settings – Check the contents of sys.configurations and the expected location of each of the system […]
[…] majority of the instance settings can be updated using sp_configure. And in fact you can use sp_configure to list out all of the settings it can update. It’s […]