SQL SERVER – Server Configurations Options – Allow updates

This option is present when sp_configure is executed or when sys.configuration is queried, but this functionality is unavailable since SQL Server 2005, it is obsolete. Direct updates to system objects are not allowed. If sp_configure is used to enable the option, this configuration has not effect. In previous versions as SQL Server 2000 this functionality is available.

Testing the option

The following code enable the option “allow updates”, then a table is created and finally an update to system table is executed:

sp_configure 'allow updates', 1

GO

RECONFIGURE with override

GO

CREATE TABLE TBLTEST (TESTFIELD INT )

GO

update sys.objects

set name = 'TBLTEST_1'

where name = 'TBLTEST'

GO

When the update to system table is triggered the following error is shown:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

This option is not advanced option and is not necessary enable the “show advanced options”. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER – Server Configurations Options – Catalog View – sys.configurations