Export (0) Print
Expand All

allow updates Option

SQL Server 2000

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 an instance of 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. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services.

Because system tables are critical to the operation of SQL Server, enable allow updates only in tightly controlled situations. Prevent other users from accessing SQL Server while you are directly updating system tables by restarting an instance of SQL Server from the command prompt with sqlservr -m. This command starts an instance of 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

Enterprise Manager

Transact-SQL

SQL-DMO

See Also

GRANT

RECONFIGURE

Setting Configuration Options

sp_configure

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft