"transfering" data to test database

What I need to do:
Copy database from one server to another.
Problem:
3rd party application requires the owner of the tables to be different.
Eg in the source db the owner is A so the name of table1 is A.Table1 on
the destination db the owner is supposed to be B so the name of table1 will
be B.Table1.
I have used a DTS package using the Copy SQL Server object task due to the
fact that I have a lot of tables however this also copies the owner accross
so it is named A.Table1 and not the required B.Table1.
Any Ideas?
Thanks.
JonasYou could try this:
1. For each table in your database
(e.g., get all tables
SELECT * FROM INFORMATION_SCHEMA.TABLES)
2. Change owner with sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
You could run a query similar to the one below, to return all "sp_changeobjectowner" statements as a recordset which you can then execute as a batch to change the ownership of all your tables at once:
e.g.,
Select 'exec sp_changeobjectowner @.objname = [youroldowner].[' + table_name + '] , @.newowner = ''yournewowner'''
from INFORMATION_SCHEMA.TABLES
Tea C.
"Jonas Larsen" wrote:
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be different.
> Eg in the source db the owner is A so the name of table1 is A.Table1 on
> the destination db the owner is supposed to be B so the name of table1 will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>
>|||Hi,
You can change the object owner using the system stored procedure
sp_changeobjectowner.
sp_changeobjectowner 'Table_name', 'B_USER'
Instead of doing one by one you could query the sysobjects table for all
objects ownerd by A_user.
set quoted_identifier off
select 'sp_changeobjectowner '+"'"+name+"',"+"'"+'b_user'+"'" +char(10)+'go'
from sysobjects
where user_name(uid)='A_user' and type='u'
Execute the above query in Query Analyzer in with Text result. (Query menu
result in text)
Copy the result in Query screen and execute to change the object owner to
B_USER.
Thanks
Hari
MCDBA
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be
different.
> Eg in the source db the owner is A so the name of table1 is A.Table1
on
> the destination db the owner is supposed to be B so the name of table1
will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner
accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>|||I beleive DTS can copy tables between different owners directly instead of
having to to do all ownership changing...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be
different.
> Eg in the source db the owner is A so the name of table1 is A.Table1
on
> the destination db the owner is supposed to be B so the name of table1
will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner
accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>|||Ok. Do you know how this is done?
Thanks.
Regards
Jonas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhug4pJdEHA.3216@.TK2MSFTNGP11.phx.gbl...
> I beleive DTS can copy tables between different owners directly instead of
> having to to do all ownership changing...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> > What I need to do:
> >
> > Copy database from one server to another.
> >
> > Problem:
> > 3rd party application requires the owner of the tables to be
> different.
> > Eg in the source db the owner is A so the name of table1 is A.Table1
> on
> > the destination db the owner is supposed to be B so the name of table1
> will
> > be B.Table1.
> >
> > I have used a DTS package using the Copy SQL Server object task due to
the
> > fact that I have a lot of tables however this also copies the owner
> accross
> > so it is named A.Table1 and not the required B.Table1.
> >
> > Any Ideas?
> >
> > Thanks.
> >
> > Jonas
> >
> >
>