June 16, 2008 at 1:16 pm
Guys,
In SQL Server 2000, one can update sysobjects table by changing the configuration parameter of 'allow update to 1. In 2005, there is sys.sysobjects table/catalog. How can I do an update or change the value of one or some of the Column/s?
June 16, 2008 at 1:17 pm
Things are more blocked out in SS2K5. AFAIK, you cannot alter the system tables.
What do you need to do?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 16, 2008 at 1:20 pm
That is my next question.
June 16, 2008 at 1:41 pm
control (6/16/2008)
How can I do an update or change the value of one or some of the Column/s?
You don't. The system tables aren't updatable in SQL 2005. They aren't even visible. sys.objects is a view.
There's no need to modify the system tables. There was no need on 2000, except perhaps to cause really odd error messages.
What are you trying to do that you feel the need to modify the system tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2008 at 1:56 pm
I would like to change the value of object owner to 1 which is dbo in sysobjects table. I have users that owns objects and they do not work for the company.
June 16, 2008 at 2:05 pm
This ought to do the trick ("real life example 2")
http://qa.sqlservercentral.com/articles/Advanced/understandingobjectownership/1966/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 2:05 pm
ALTER AUTHORIZATION On <Object name> TO <New Owner>
There are a lot of different options for ALTER AUTHORIZATION, so check what Books online has to say on the subject
That's if it is the object's owner you're talking about. If you mean that you want to change the schema that the object is in (schema and owner are different on SQL 2005), then this is more what you're looking for:
ALTER SCHEMA <new schema name> TRANSFER <Object name>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2008 at 2:08 pm
Is the database in 80 or 90 compatibility mode? If it's 90, you're in SQL Server 2005 mode, meaning the objects aren't owned, they belong to schemas which are owned. You can change the ownership of the schema to dbo, meaning all of your objects references (schemaname.objectname) don't change if two-part naming conventions are being used.
K. Brian Kelley
@kbriankelley
July 24, 2008 at 2:09 pm
I ahve looked around a lot and yet to find the process to change table owner. Can someone pls show with an example. like in sql 2000 i used sp_changeobjectowner procedure. In sql 2005 how do you change:
abc.employee to dbo.employee
appreciate u'r help.
July 24, 2008 at 4:38 pm
You can alter system tables as per
link but better to stay away from such options.
You can put the SERVER into single-user mode, then connect with the DAC and you can then update the system tables.
Manu
July 25, 2008 at 9:12 am
What is DAC?
July 25, 2008 at 9:27 am
DAC stands for Dedicated administraotr connection-New feature introduced in sql 2005. SQL Server 2005 provides a special diagnostic connection for administrators when standard connections to the server are not possible.
http://msdn.microsoft.com/en-us/library/ms189595.aspx
Manu
July 25, 2008 at 9:29 am
Thanks very much.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply