Tuesday, August 18, 2015

Transfer Login Task

Transfer Login Task



Transfer Logins Task is used to transfer either all logins (except sa) or selected logins or all logins of selected databases from the source to the destination SQL Server instance.  After the transfer, all the transferred SQL logins are assigned random passwords and SQL logins are disabled. The DBA needs to change the password and enable the SQL login before it can be used on the destination.
Let's walk through an example. Create another package in the current project and drag aTransfer Logins Task from the Toolbox to the Control Flow. Right click on the task and select Edit to configure the task's properties as shown below.

These are the items that can be configured:
  • Connections
    • SourceConnection - specify the connection for the source SQL Server instance
    • DestinationConnection - specify the connection for the destination SQL Server instance
  • Logins
    • LoginsToTransfer - You have three options for this:
      • AllLogins - this will transfer all logins from the source.
      • SelectedLogins - this allows you to select specific logins
      • AllLoginsFromSelectedDatabases - This allows you to transfer all logins associated with one or more databases as shown in the image below.
    • LoginsList - this will allow you to select specific logins if you selectSelectedLogins for LoginsToTransfer
    • DatabaseList - this will allow you to select the databases if you select AllLoginsFromSelectedDatabases for LoginsToTransfer
  • Options
    • IfObjectExists - If the logins already exist on the destination you have three choices; first FailTask execution, second Overwrite the destination logins and third Skip the existing login and continue with the others.
    • CopySids - if you set it to True then security identifiers (SIDs) associated with logins are also copied to the destination