I just had a busy travelling week-end, and hence today, we will start things off this week with a little incident that I encountered a while back. As with any development team, we have a bunch of servers lying around that are shared across the team purely for RND purposes. I had to run a particular test on one of these environments and that’s when this happened.

The situation was that my test required me to enable the xp_cmdshell feature of Microsoft SQL Server 2008. Hence, I proceeded with the following set of scripts, which are pretty straight-forward and “should” work on all environments, provided I have the appropriate permissions. I was connected to the server with the “sa” login and used the RECONFIGURE command so that I do not need a SQL Server restart for the changes to take effect.

sp_configure 'show advanced options',1;
reconfigure
go

sp_configure 'xp_cmdshell',1;
reconfigure
go

Although the scripts are quite simple and was connected using the highest possible privileges, the SQL Server just would not allow me to apply the changes. The error that it threw was as under:

Msg 5808, Level 16, State 1, Line 3

Ad hoc update to system catalogs is not supported.

As the test was somewhat urgent, I proceeded with the following work-around.

sp_configure 'show advanced options',1;
reconfigure with override
go

sp_configure 'xp_cmdshell',1;
reconfigure with override
go

Later in the day, I did some research and found that a team member had been toying around with the SQL Server as a practical hands-on experience for one of the examinations that the team member was preparing for. However, he had forgotten to roll-back the changes made by him before he put the server back into the “available” repository – which is what was causing the problem.

SQL Server has a setting called – “allow updates”. This setting was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. This setting is now deprecated since SQL Server 2005 and all SQL Server installations come with this set to 0 by default. Per Books On Line (read the BOL help on this option here), this setting has no effect, except that the RECONFIGURE statement will fail, just as it is doing in our case. Running the following statement to reset this back to 0 did the trick and I was now able to use RECONFIGURE again to reset the environment back to the “factory default”.

-- Reset the "allow updates" setting to the recommended 0
sp_configure 'allow updates',0;
reconfigure with override
go

-- Reset the environment back as the test is complete
sp_configure 'xp_cmdshell',0;
reconfigure
go

sp_configure 'show advanced options',0;
reconfigure
go

Lessons Learnt

This incident has refreshed in my memory the two basic rules that I have been taught by my father (way back when I was introduced to computers with Windows 3.1!)

1. Irrespective of whether you are doing a production change or a local RND change, always keep a log of the changes made
2. Once done, if the change was a purely researching change, always roll the environment back to the condition you found it in

Here’s something I don’t understand

By default, a user cannot see the advanced configuration options. Even when you run sp_configure on a clean install of SQL Server 2008, the “allow updates” option is right at the top! Why? If the product is not expecting users to use this setting, it should be hidden from the users view. If a user sees something, he/she is bound to use it.

If you work with a product and if you get a say during product evolution, request you to ensure that things which a user should not be using are either hidden from the user’s view, or are at least hard to access.

Until we meet next time,

Be courteous. Drive responsibly.