Login mapping lost after "Copy SQL Server Objects"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Author

Topic

anniech2000
Starting Member

12 Posts

Posted - 2008-05-06 : 03:38:32

After I run a SSIS package which is a "Trasfer SQL Server Objects Task", the database users has lost all mapping to SQL server login. I've tried various options inside the package, however, to my surprise, sometimes the user settings at SOURCE database is lost! I won't suppose to change anything in the SOURCE database after running a data transfer... Anyone knows what's wrong?
Thanks in advance!

Options:
DropObjectsFirst - TRUE
IncludeExtendedProperties - FALSE
CopyData - TRUE
ExistingData - Replace
CopySchema - TRUE
UseCollation - FALSE
IncludeDependentObjects - TRUE
CopyAllOBjects - TRUE
CopySqlServerLogins - TRUE/FALSE (both tried)

CASE 1:
CopyDatabaseUsers - TRUE
CopyDatabaseRoles - TRUE
Result - the user-login mapping at destination DB is lost.

CASE 2:
CopyDatabaseUsers - FALSE
CopyDatabaseRoles - FALSE
Result - the user-login mapping at SOURCE DB is lost.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-06 : 21:53:51

Is it on sql2k5? What's sp level?

anniech2000
Starting Member

12 Posts

Posted - 2008-05-06 : 22:00:06

Yes it is on SQL Server 2005, with sp2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-07 : 13:56:22

What exactly are you trying to transfer?

anniech2000
Starting Member

12 Posts

Posted - 2008-05-07 : 21:41:11

I am trying to create a package to transfer all or some of the tables from one database to another. Now I found that if I choose "CopyAllObjects" = TRUE, then user-login mapping will be lost. If I choose "CopyAllObjects" = FALSE but set all options underneath TRUE, then user-login mapping will remains there but the user-role mapping will be lost.