Previously I have posted an article for transferring logins between instances using stored procedures sp_hexadecimal and sp_help_revlogin. Here in this article I am going to use another method to transfer logins between two instances.
Using BIDS you can transfer either all the logins or particular logins from desired database or all logins except sa from source server to destination server. But the downside is after the transfer, the sql logins are assigned some random passwords and all the logins are disabled. You need to change the password and enable the login manually before it can be used on the destination.
Steps to create an ssis package to transfer the logins:
- Open an SSIS project
- Give a relevant name to the package and select the path to save the project
- Drag and drop the Transfer Login Task from toolbox to control flow area.
- Right click on the task and select edit
- Specify the source and destination server
- Choose the logins that you need to transfer.
- LoginsToTransfer - You have three options for selecting logins:
- 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 select SelectedLogins for LoginsToTransfer
- DatabaseList - This will allow you to select the databases if you select AllLoginsFromSelectedDatabases for LoginsToTransfer
- Choose the desired option in options section.
- IfObjectExists - If the logins are already exists on the destination you can select any of these 3 choices. First FailTask execution, second Overwrite the destination logins and third Skip the existing login and continue with the others.
- CopySids - if you set this option to True then security identifiers (SIDs) will also transfer to the destination
- Now execute the package and check the logins are transferred to the destination server. Note: Only sysadmin can execute the package
