Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

• AshikWani

  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.
  

• Johnathon

  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

  
  
  

• shamim

  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

  
  

• Jay B. [Mobius Labs]

  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

  
  

• Peter Cwik

  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
  
  

• Frank Leonardi

  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.

   

  
  

• PatMc

  Hi, I'm having the same problem, what was your solution
  
  

• JesperChristensen

  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
  
  
  

• *Kelsey*

  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.

  
  

• E00Y

  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

  
  
  

• Normajean

  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.
  
  

• naama

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

• tri_phani

  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.
  
  

• Ritesh Singh

  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.

  
  
  
• Ad hoc updates to system catalogs are not allowed.