x

Ad hoc update to system catalogs is not supported

I try changing the "Show advanced options" setting by running

exec sp_configure 'show advanced options', 1; reconfigure; go

but get the message 5808, Level 16, State 1, Line 2 Ad hoc update to system catalogs is not supported.

I can't seem to find how to change this setting. Any ideas?

Interestingly, the setting "Allow updates" has a config_value and run_value of 1.
more ▼

asked Jun 01 '11 at 05:24 AM in Default

xnl28 gravatar image

xnl28
895 54 58 61

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It is worth checking that you have the appropriate permissions...

From [sp_configure (Transact-SQL) on MSDN][1]:

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

[1]: http://msdn.microsoft.com/en-us/library/ms188787.aspx
more ▼

answered Jun 01 '11 at 05:44 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

If you try running reconfigure without sufficient permissions you get the error "Msg 5812, Level 14, State 1, Line 1 You do not have permission to run the RECONFIGURE statement.". Do people on this site vote answers up purely on the basis of the reputation of the answerer or something?
Jun 02 '11 at 03:07 AM Martin 1
(comments are locked)
10|1200 characters needed characters left

You will get that message every time that you run reconfigure whilst that setting is in effect unless you also specify with override

AFAIK this setting is completely obsolete now so you might as well turn the option off.

If you actually do need to update the system tables the only way this is allowed is with the instance started in single user mode and connecting via the DAC. This is not supported/recommended.
more ▼

answered Jun 01 '11 at 06:04 AM

Martin 1 gravatar image

Martin 1
203 8 9 11

Right on @Martin. The comment by @Oleg to @megamanblue illustrates this perfectly if you change "allow updates" to 1 and remove "with override" from the "show advanced options" portion
Jun 02 '11 at 11:55 AM KenJ
(comments are locked)
10|1200 characters needed characters left

I do not believe you can. The command to enable this would be sp_configure 'allow updates', 1. (Double check BOL). However, I believe the ability to perform updates on system catalogs has been removed since SQL 2005.

On second thoughts... There may be a way using DAC.
more ▼

answered Jun 01 '11 at 05:47 AM

megamanblue gravatar image

megamanblue
310 6 6 8

This is absolutely correct. To get rid of the message in question, the following probably needs to happen:

sp_configure 'Allow Updates', 0;
reconfigure with override;
go

-- and just in case:
sp_configure 'Show Advanced Options', 1;
reconfigure with override;
go
At this time Show Advanced Options should be set to 1 as desired, and there would be no message about unsupported value of 1 for 'Allow Updates' setting. The latter is evil and unsupported, so it should be set to where it belongs: zero :)
Jun 01 '11 at 08:37 AM Oleg
I agree. Evil! Unless of course you are a disgruntled DBA and want to go out with a bang ;)
Jun 01 '11 at 08:42 AM megamanblue
Goodness me, but that would be completely unprofessional. I can't possibly put a +1 on that comment, @megamanblue!
Jun 01 '11 at 11:43 AM ThomasRushton ♦
@Blackhawk-17 - This is easy to test and I have done logged in as sa. Nothing to do with permissions as far as I can see.
Jun 02 '11 at 02:08 AM Martin 1
@xnl28 Can you try the snippet in my comment above? The error you are getting might be simply caused by the fact that when the setting to show advanced options is set to 1 then SQL Server notices the bad value in the 'Allow Updates' and barks. Since the latter should be set to 0 anyway, set it so and then running the setting for advanced options should execute without any errors.
Jun 02 '11 at 06:58 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x562

asked: Jun 01 '11 at 05:24 AM

Seen: 13607 times

Last Updated: Jun 01 '11 at 05:24 AM