Change Table Owner Question

Hi Experts,

there's a lot of posts about using exec sp_changeobjectowner 'tablename.table', 'dbo' to rename tables back to dbo ownership if they have been created by another owner, but can someone tell me how to specify the database?

Basically we have a live and a dev database - they live is running fine and I don't want to touch it - but want to change the table owner on dev for some tests....

I don't want to run the above command on both - just the dev database...

This is pretty urgent so I've given it max points

Many thanks,
pjordannaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ColosseoConnect With a Mentor Commented:
Hi

If you select the Dev database in Enterprise Manager Then if you open query analyser using Tools > Query Analyzer

Then run the sp_changeobjectowner

Or you could type USE [database name] before the stored procedure

Regards

Scott
0
 
pjordannaAuthor Commented:
thanks Scott!
0
 
ColosseoCommented:
Your welcome, glad to help :)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rakhareCommented:
Is there a batch method to do this? I need to do the same thing to about 30 tables in the same db.
0
 
brickredtechCommented:
Run followinf sql

Select 'exec sp_changeobjectowner ''ididb.' + name +''', ''dbo''' script
from sysobjects
where xtype = 'U' and uid in (Select UID from sysobjects
where xtype = 'U' and name = '[TABLENAME]')
order by name

Here replace [TABLENAME] with any table's name whose owner you wish to change. it will actually return sql script in each row to change the owner (in your case 30 rows). You only need to copy-paste the output and need to execute this in query analyzer.
0
 
mmandraxCommented:
That seemed painful, so I looked around a bit and found this...just change someusername to the current user and execute....



DECLARE tabcurs CURSOR
FOR
    SELECT 'someusername.' + [name]
      FROM sysobjects
     WHERE xtype = 'u'

OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0
BEGIN

    EXEC sp_changeobjectowner @tname, 'dbo'

    FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.