October 23, 2014 08:03 by
PeterI was recently asked where to go in Configuration Manager to vary the settings for xp_cmdshell on MSSQL 2014 Hosting. it was then that i spotted that the existence of sp_configure is maybe not universal data. I used to be actually afraid. for a few reason I had thought that DBA’s were born with this data. As this seems to not be the case, I felt it might be an honest plan to allow sp_configure a quick run through. Note: This keep procedure modifies system settings. Make certain you understand the setting that you're changing and the need for the change before you creating it.
The system keep procedure sp_configure is used to view or modify variefty of various system settings. These settings will include the default MAXDOP for the server, whether or not SMOs is enabled, whether xp_cmdshell is enabled and an extended list of others. to urge a whole list you'll either question sys.configurations or run sp_configure with no parameters.
Why is that the list from sp_configure shorter than the list on sys.configurations? If I will modify xp_cmdshell victimisation sp_configure. Some configurations are thought-about Advanced. If you would like to see them you may need to change the setting show advanced choices (using of course sp_configure).
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
The Reason why you're adding during this RECONFIGURE command, because sp_configure solely changes the configured value. RECONFIGURE updates the running value with the configured value (there are some exceptions wherever a reboot or instance restart is required). thus in order to actually modification the currently running configuration value you have got to use each commands. as luck would have it RECONFIGURE checks that the configured value is valid before setting it because the running value.
If you're fully bound you recognize what you're doing and want to update one of the configuration values with one thing outside of its allowed vary you'll be able to embrace the WITH OVERRIDE choice of the RECONFIGURE command. So here’s the factor, if you're aiming to be administrating SQL Server you would like to have at least a passing familiarity of the sp_configure and RECONFIGURE commands and a reasonable idea of what settings they modify.