Solved

Change Table Owner Question

Posted on 2004-08-11
6
42,398 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Accepted Solution

by:
Colosseo earned 500 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

735 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