Using sp_configure

sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. When executed without parameters, this procedure will enumerate the basic configuration settings on the SQL Server.

[cc lang=”sql”]
EXEC sp_configure
[/cc]

These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options this procedure does not show us unless we change an additional configuration setting, named, ‘show advanced options’. Let’s do that now.

[cc lang=”sql”]
EXEC sp_configure ‘show advanced options’, 1 — Changing to 1 turns it on
GO
RECONFIGURE WITH OVERRIDE
[/cc]

Now that we have changed this option, let’s execute sp_configure again and look at the result.
[cc lang=”sql”]
EXEC sp_configure
[/cc]

Now we should see upwards of 70 configuration options we are able to set on SQL Server.

In order to set them, we only need to copy the exact name listed in the ‘name’ column, and pass it as the first parameter for sp_configure, then for the second parameter, pass our desired value.

[cc lang=”sql”]
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE WITH OVERRIDE
[/cc]