[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change Table Owner Question

Posted on 2004-08-11
6
Medium Priority
?
42,412 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 2000 total points
ID: 11771135
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
ID: 11771234
thanks Scott!
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 11791412
Your welcome, glad to help :)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Expert Comment

by:rakhare
ID: 14479183
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
ID: 21129082
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
ID: 24142912
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 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