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, 2011 at 05:24 AM in Default

avatar image

905 60 64 68

(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:

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.

more ▼

answered Jun 01, 2011 at 05:44 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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, 2011 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, 2011 at 06:04 AM

avatar image

Martin 1
203 8 11 16

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, 2011 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, 2011 at 05:47 AM

avatar image

310 7 6 11

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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jun 01, 2011 at 05:24 AM

Seen: 23412 times

Last Updated: Jun 01, 2011 at 05:24 AM

Copyright 2018 Redgate Software. Privacy Policy