SQL Server Performance

User mapping lost after running a SSIS package

Discussion in 'SQL Server 2005 Integration Services' started by anniech2000, May 6, 2008.

  1. anniech2000 New Member

    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!
    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.
  2. satya Moderator

    Welcome to the forums.
    What is the service pack level on SQL Server instance used here?
    It shouldn't happen and see what Books Online refers about this task:
    The Transfer SQL Server Objects task can be configured to transfer all objects, all objects of a type, or only specified objects of a type. For example, you can choose to copy only selected tables in the AdventureWorks database.
    If the Transfer SQL Server Objects task transfers tables, you can specify the types of table-related objects to copy with the tables. For example, you can specify that primary keys are copied with tables.
    Server roles, roles, and users from the specified database can be copied, as well as the permissions for the transferred objects. By copying the associated users, roles, and permissions together with the objects, you can make the transferred objects immediately operable on the destination server.
    To further enhance functionality of transferred objects, you can configure the Transfer SQL Server Objects task to include schema names, data, extended properties of transferred objects, and dependent objects in the transfer. When copying data, you can specify whether to replace or append existing data.
    At run time, the Transfer SQL Server Objects task connects to the source and destination servers by using two SMO connection managers. The SMO connection managers are configured separately from the Transfer SQL Server Objects task, and then referenced in the Transfer SQL Server Objects task. The SMO connection managers specify the server and the authentication mode to use when accessing the server.
  3. anniech2000 New Member

    Thanks, I will do more testing on copying just one or two tables.
    BTW, we have applied service pack 2 and the current version is SQL Server 2005 - 9.00.3042.00(X64)
    The problem is strange and I also wonder whether it is because of my security settings or something else...
  4. satya Moderator

    Were there any changes to the windows side of your login permissions?
    Are the both SQL instances are in SQL 2005 SP2?>
  5. anniech2000 New Member

    All the server logins remain unchanged. Only users mapping with these logins are removed. And we don't use windows authentications. Is that what you mean?
    Actually I am just copying a A database to a B database in the same instance.
    And I found that the user mappings will not be lost if i choose individual tables, i.e. CopyAllObjects is set to false.

Share This Page