allow updates Option

Use the allow updates option 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.

Caution Updating fields in system tables can prevent Microsoft® SQL Server™ from running or can cause data loss. If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates.

Because system tables are critical to the operation of SQL Server, you should enable allow updates only in tightly controlled situations. You can prevent other users from accessing SQL Server while you are directly updating system tables by restarting SQL Server from the Microsoft Windows® command prompt with sqlservr -m, which starts SQL Server in single-user mode and enables allow updates. For more information, see Starting SQL Server with Minimal Configuration.

If you set allow updates to 1 using the sp_configure system stored procedure, you must use the RECONFIGURE WITH OVERRIDE statement. This setting takes effect immediately (without a server stop and restart).

To set the allow updates option

See Also