ms sql 2005 Changing the owner of a table

Posted on 2007-10-19
Medium Priority
Last Modified: 2012-06-27
I have 2  ms sql 2005 servers when I export from server1 to server2 tables in server2 appear as owned by server1 example:

instead of table dbo.names (as was in server1) I get server1.names in server2. It is not neat for me at this moment is there a way to change the owner or export or import in a way?
Question by:robrodp
  • 4
  • 3
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20109948
you can change the object owner by using the system sp, sp_ChangeObjectOwner

Author Comment

ID: 20109981
Mh... could I get some detaild syntax....
LVL 31

Accepted Solution

James Murrell earned 2000 total points
ID: 20151848
Hope this helps......

sp_changeobjectowner [@objname =] 'object', [@newowner =] 'owner'

[@objname =] 'object'
Is the name of an existing table, view, or stored procedure in the current database. object is nvarchar(517), with no default. object can be qualified with the existing object owner, in the form existing_owner.object.
[@newowner =] 'owner'
Is the name of the security account that will be the new owner of the object. owner is sysname, with no default. owner must be a valid Microsoft® SQL Server" user or role, or Microsoft Windows NT® user or group in the current database. When specifying Windows NT users or groups, specify the name the Windows NT user or group is known by in the database (added using sp_grantdbaccess).
Return Code Values
0 (success) or 1 (failure)

The owner of an object (or the members of the group or role owning the object) has special permissions for the object. Object owners can execute any of the Transact-SQL statements related to the object (for example, INSERT, UPDATE, DELETE, SELECT, or EXECUTE) and can also manage the permissions for the object.

Use sp_changeobjectowner to change the owner of an object if the security account that owns the object has to be dropped but the object must be retained.

Use sp_changedbowner to change the owner of a database.

Only members of the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner.

This example changes the owner of the authors table to Corporate\GeorgeW.

EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 20156040

Is there a way to make a global change of owners set all tables to dbo' Not one by one?
LVL 31

Expert Comment

by:James Murrell
ID: 20156280
yes by using the sp__foreachtable as well

Author Comment

ID: 20157661
Hi for 1 table:

EXEC  sp_changeobjectowner 'siembra', 'dbo'

it works

Where do I fit sp__foreachtable?
LVL 31

Expert Comment

by:James Murrell
ID: 20169092
i foget of hand but i think it is something like

EXEC  sp__foreachtable 'EXEC  sp_changeobjectowner 'siembra', 'dbo''
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20169221
LVL 31

Expert Comment

by:James Murrell
ID: 20172155
in fact try this

    SELECT 'SOMEOWNER.' + [name]
      FROM sysobjects
     WHERE xtype = 'u'

OPEN tabcurs
FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0

    EXEC sp_changeobjectowner @tname, 'dbo'

    FETCH NEXT FROM tabcurs INTO @tname
CLOSE tabcurs

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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