How to change SQL Server configuration options and differences between reconfigure & reconfigure with override.

For DBA’s there will definitely be situations where we need make SQL Server configuration changes. We might be forced to make changes while performing any monthly/weekly maintenance tasks or might be any of newly deployed SQL jobs require to use new options of SQL server something like if we have new job that will need to send mail to DBA team after completing successfully, to achieve this the option ‘Database Mail XPs’ should be enabled. Same way in some situations we might need to verify whether a particular server have access to a network location for this the option ‘xp_cmdshell’ should be enabled like these there will be number of occasions where we need to make changes to SQL server configuration options.

We can find the existing SQL server configuration options and also can make changes by using ‘sp_configure’.

TO Display current SQL Server Configuration Settings:

SP_Configure

This will display current setting and values of configuration options. It will have 5 column output. Name is the name of setting, minimum is the minimum value that can be set and maximum is the maximum value that can be set, config_value is value currently set to configuration option, run_value is current running value of configuration option.

If the config_value and run_value currently set to ZERO it means that option is disabled.

To Change Existing Value:

SP_Configure ‘Configuration Option’ , Value

RECONFIGURE

Example: I will enable ‘XP_CmdShell’.

SP_Configure ‘xp_cmdshell’ , 1

This command will give below output after execution.

Configuration option ' xp_cmdshell ' changed from 0 to 1. Run the RECONFIGURE statement to install.

This is because above command will change only the configuration value and to change the running value we need to execute ‘RECONFIGURE’’. So to complete the change we have to run ‘RECONFIGURE’ command.

RECONFIGURE

(or)

RECONFIGURE WITH OVERRIDE

Both the above commands are to change the running value itself but there is slight difference between them.

RECONFIGURE:

This make the changes only if the new value is valid/allowed or in documented valid ranges. If the value doesn’t meet the standard or not recommended this command will fail.

RECONFIGURE WITH OVERRIDE:

This makes changes to running value of configuration options without verifying weather the new value is in valid range/documented or not.

Coming back to enabling ‘xp_cmdshell’ below commands will fulfill the requirement:

SP_Configure ‘xp_cmdshell’ , 1

RECONFIGURE WITH OVERRIDE

In the same way we can change any of the configuration options.

Sometime while performing configuration changes we might receive below error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option ' xp_cmdshell ' does not exist, or it may be an advanced option.

This happens when the ‘show advanced options’ configuration option was in disabled state. This configuration option should be enabled in order to make changes to any of the advanced options and to enable this option we can follow the same method mentioned above.

sp_configure 'show advanced options', 1

reconfigure

Thanks

Suggestions/Feedback always needed.

How to grant execute on all stored procedures?

In order to grant EXECUTE permission on all Stored Procedures of a database to a user we can do it in several ways one way is...

To achieve this we can create a user defined role and grant it the EXECUTE permission and then add the user we want to grant the EXECUTE permission.

User Defined Role Creation:

Create role SP_Executor

Granting EXECUTE permission to the role created:

Grant EXECUTE to SP_Executor

Add user to the role :

Exec SP_Addrolemember 'SP_Executor','DomainName\UserName’

Thanks