Update table owner (UID) error

Question

Update table owner (UID) error

rodan-892007

Right there with Babe

Points: 731
More actions
September 21, 2009 at 9:39 am

#136062

I used to run the following in SQL 2000, but in 2005 I get an "ad hoc updates to system catalogs are not allowed". I'm basically trying to update my table owners. What is the equivalent to sp_configure in 2005? Or if there isn't one, what should I do to update these table owners?
EXEC sp_configure 'Allow Updates', '1'
RECONFIGURE
UPDATE JDE_DEVELOPMENT.dbo.SYSOBJECTS SET UID = (SELECT UID FROM JDE_DEVELOPMENT.dbo.SYSUSERS WHERE NAME='TESTCTL')
WHERE UID = (SELECT UID FROM JDE_DEVELOPMENT.dbo.SYSUSERS WHERE NAME='PRODCTL')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think you'll need to use the stored procedure sp_changeobjectowner, which i think was around for SQl 2000 as well; that's the way to change the owner from say ,dbo, to the user webdev, for example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rodan-892007 Right there with Babe Points: 731 More actions · Answer 2

Looks like that will work, but I need to have it select all the situations where the owner id PRODCTL. The SP's syntax is: sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

In other words, can this be used to change multiple objects at once? There are hundreds of tables...

Lowell SSC Guru Points: 323518 More actions · Answer 3

looks like you'll need to make a cursor, since the proc only changes one object at a time;

at least with the cursor, you could call it with parameters for all your objects.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rodan-892007 Right there with Babe Points: 731 More actions · Answer 4

that sp destroys all my table permissions, looking into others...