ms sql 2005 Changing the owner of a table

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?
robrodpAsked:
Who is Participating?
 
James MurrellProduct SpecialistCommented:
Hope this helps......

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

Arguments
[@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)

Remarks
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.

Permissions
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.

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

EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW'

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can change the object owner by using the system sp, sp_ChangeObjectOwner
0
 
robrodpAuthor Commented:
Mh... could I get some detaild syntax....
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
robrodpAuthor Commented:
Great...

Is there a way to make a global change of owners set all tables to dbo' Not one by one?
0
 
James MurrellProduct SpecialistCommented:
yes by using the sp__foreachtable as well
0
 
robrodpAuthor Commented:
Hi for 1 table:

EXEC  sp_changeobjectowner 'siembra', 'dbo'

it works

Where do I fit sp__foreachtable?
0
 
James MurrellProduct SpecialistCommented:
i foget of hand but i think it is something like

EXEC  sp__foreachtable 'EXEC  sp_changeobjectowner 'siembra', 'dbo''
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
James MurrellProduct SpecialistCommented:
in fact try this

DECLARE tabcurs CURSOR
FOR
    SELECT 'SOMEOWNER.' + [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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.