Solved

Change Table Owner Question

Posted on 2004-08-11
6
42,389 Views
Last Modified: 2012-05-05
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
Comment
Question by:pjordanna
6 Comments
 
LVL 15

Accepted Solution

by:
Colosseo earned 500 total points
Comment Utility
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
 

Author Comment

by:pjordanna
Comment Utility
thanks Scott!
0
 
LVL 15

Expert Comment

by:Colosseo
Comment Utility
Your welcome, glad to help :)
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Expert Comment

by:rakhare
Comment Utility
Is there a batch method to do this? I need to do the same thing to about 30 tables in the same db.
0
 

Expert Comment

by:brickredtech
Comment Utility
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
 

Expert Comment

by:mmandrax
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now