sql server dts: I have created a DTS Package with a Copy Objects step. This copy objects
step copies some tables, their indexes, and data to a server that is housed
at an ISP. The account that I log in with on the external server is a dbo
for the database I'm targeting, but my permissions are limited to that
database. I have unchecked the options that try to copy user and permissions
to the external server.

When I run this package, I get the following error: Server user xxx is not
a valid user in database yyy

Database yyy is not the database that I'm trying to target, and is not a
database I have permissions to at all because it is a database that belongs
to another client of the ISP. I have tried to work with the ISP with regards
to this, and sometimes the problem has temporarily disappeared, but it always
comes back and does not always seem to be fixed by the same process.

I have heavily researched this issue on the net, and it looks like it's
likely caused by some sort of corruption/incorrect entries in the security
tables, but I'm not sure what I can do to get around it. Is there something
I can do (with the limitations imposed on my user account by the ISP, being a
dbo only) that can reliably fix this problem (even if it is a temporary fix
that I must perform each time I want to transfer the objects)?

Any help would be greatly appreciated.

Hi,

Thanks for your posting!

From your descriptions, I understood that you are notified that user is not
valid user in database while the database is not the one you wanted. Have I
understood you? Correct me if I was wrong.

Have you RESTORE the database from other SQL Server? Based on my scope,
this is due to the fact that the user though a dbo of the database, since
the SIDs are different on each server the mapping between the SQL logins
and the user databases is lost.

You should use SA account to run sp_change_users_login to fix the the
orphaned users. Here is some Knowledge Base describing this issue for your
reference:

PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/kb/168001

HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133

Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are always here to be of assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!