Ad hoc update to system catalogs is not supported; sp_configure 'allow updates'



"Allow Updates" is sp_configure option which is use to specify whether direct updates can be made to system tables.

         In SQL 2014 this option is still present in the sp_configure stored procedure, although its functionality is unavailable in SQL Server. The setting has no effect. Direct updates to the system tables are not supported.
      Then why its there?
                  This option becomes useless since sql 2000 but never recommended to use. If you try to change configuration value to 1 it use to throw error but now it just executes successfully without any change
                 If due to some server level activity this value get modified then we have to reset same to '0' in case of below error.

Error: "Ad hoc update to system catalogs is not supported"

Syntax:
sp_configure 'allow updates' ,0
Go 
reconfigure with override
GO

MSDN Definition: "allow updates" option is use to specify whether direct updates can be made to system tables. By default, allow updates is disabled (set to 0), so users cannot update system tables through ad hoc updates. Users can update system tables using system stored procedures only. 
                 When allow updates is disabled, updates are not allowed, even if you have the appropriate permissions (assigned using the GRANT statement). When allow updates is enabled (set to 1), any user who has appropriate permissions can update system tables directly with ad hoc updates and can create stored procedures that update system tables.

Thanks for "Tinnu Babu T Paily" for documenting this for me

No comments:

Post a Comment