Transfer Logins Task
The Transfer Logins task transfers one or more logins between instances of SQL Server.
The Transfer Logins task can be configured to transfer all logins, only specified logins, or all logins that have access to specified databases only. The sa login cannot be transferred. In SQL Server 2000 and later the sa login may be renamed; however, the renamed sa login cannot be transferred either.
You can also indicate whether the task copies the security identifiers (SIDs) associated with the logins. If the Transfer Logins task is used in conjunction with the Transfer Database task the SIDs must be copied to the destination; otherwise, the transferred logins are not recognized by the destination database.
At the destination, the transferred logins are disabled and assigned random passwords. A member of the sysadmin role on the destination server must change the passwords and enable the logins before the logins can be used.
The logins to be transferred may already exist on the destination. The Transfer Logins task can be configured to handle existing logins in the following ways:
- Overwrite existing logins.
- Fail the task when duplicate logins exist.
- Skip duplicate logins.
At run time, the Transfer Logins task connects to the source and destination servers by using two SMO connection managers. The SMO connection managers are configured separately from the Transfer Logins task, and then referenced in the Transfer Logins task. The SMO connection managers specify the server and the authentication mode to use when accessing the server. For more information, see SMO Connection Manager.
Transferring Logins Between Instances of SQL Server
The Transfer Logins task supports a source and destination that is SQL Server 2000 or SQL Server 2005. There are no restrictions on which of the two versions to use as a source or destination.
Events
The task raises an information event that reports the number of logins transferred and a warning event when a login is overwritten.
The Transfer Logins task does not report incremental progress of the login transfer; it reports only 0% and 100 % completion.
Execution Value
The execution value, defined in the ExecutionValue property of the task, returns the number of logins transferred. By assigning a user-defined variable to the ExecValueVariable property of the Transfer Logins task, information about the login transfer can be made available to other objects in the package. For more information, see Integration Services Variables and Using Variables in Packages.
Log Entries
The Transfer Logins task includes the following custom log entries:
- TransferLoginsTaskStarTransferringObjects This log entry reports the transfer has started. The log entry includes the start time.
- TransferLoginsTaskFinishedTransferringObjects This log entry reports the transfer has completed. The log entry includes the end time.
In addition, a log entry for the OnInformation event reports the number of logins that were transferred, and a log entry for the OnWarning event is written for each login on the destination that is overwritten.
Security and Permissions
To browse logins on the source server and to create logins on the destination server, the user must be a member of the sysadmin server role on both servers.
Configuring the Transfer Logins Task
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, click one of the following topics:
- Transfer Logins Task Editor (General Page)
- Transfer Logins Task Editor (Logins Page)
- Expressions Page
For more information about how to set these properties in SSIS Designer, click the following topic:
Configuring the Transfer Logins Task Programmatically
For more information about programmatically setting these properties, click the following topic:
See Also
Concepts
Integration Services Tasks
Creating Package Control Flow
Other Resources
sp_change_users_login (Transact-SQL)