Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.|||Thanks Greg, but it doesn't help. The question is like follows:
I restore a database from an other server, the schema (object owner) is not dbo but -for example- abcd. In sql2000 I could update the sid in the sysusers-table from master.dbo.sysxlogins to enable the connection across odbc. In sql2005 there is no sysusers-table in the restored db and no sysxlogins-table in the master-db. If I try to connect across odbc I get the message "Cannot open user default database. Login failed."
Greetings hafi|||direct updates to the system tables were never supported in SQL Server. But it looks like, in SQL 2005 they are not even ALLOWED and that is a wrong move from Microsoft. Though not supported, sometimes you can't get away without updating system tables.

For ex., try moving a log shipped database from one server to another, without losing it's synchronization.
|||Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

|||I tried through DAC (sqlcmd -A) and still can't update system tables. As per BOL, DAC allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. But, it doesn't say to anything about updating system tables.

Laurentiu Cristofor wrote:

Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.

Thanks
Laurentiu

|||

I omitted to say that the server also has to be started in single-user mode, for the updates to be allowed (using the -m flag). By itself, DAC will allow you to see system tables, but to update them, you also need to have the server started in single-user mode.

Thanks
Laurentiu

|||Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

|||I believe you can do exec your_db.sys.sp_rename to make sp_rename execute in your_db. Also if you do "use your_db; sp_rename", this will be equivalent to above. Most of system sps were moved to resource database (you can see them in sys.system_objects catalog) in SQLServer 2005. They are nowis user-db neutral and will take current db context as the one to execute in.|||Hi, I'm having the same problem, what was your solution?|||

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

All these answers are great workaround-coverage for not allowing ad hoc updates to the catalog tables. And I can see some reasoning behind not allowing DBAs to get their job done in an efficient and expedient manner. But this is just another example of executive thinking --and decision making-- by MS.

My case is hundreds of databases and no way to use the wonderful method of set processing to get the job done. Now I am begin told if I want to set the status of a hundred databases from 'Full' to 'Simple' recovery, I have to use the mouse? No way!

Will I have to hack my way into one of the 'system' procs to get the job done or is there a system function available? I do not --repeat-- do not want to place the entire server in SUM (-m) just to make an update to the catalogs.

If I need to update the catalogs, the functionality of sp_configure 'allow up', 1 and reconfigure with override needs to start working again, or the configuration function needs to allow full access to all options.

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

For your other comments, you should post them on a related forum (SQL Server Database Engine, for example). If you do not receive a useful suggestion or workaround for your issue, please open a request at:

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu