• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 42414
  • Last Modified:

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,
0
pjordanna
Asked:
pjordanna
1 Solution
 
ColosseoCommented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now