SQL Server Integration Services Transfer Logins

Introduction

In the previous article, database maintenance tasks were introduced. The first maintenance task was the Transfer Database Task which is the most common maintenance task. The next task and last maintenance task to be covered is the Transfer Logins Task which closely resembles and operates much like the Transfer Database Task.

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

Scenario

Last time a database was being moved to a new server, so in this article the scenario will be moving database logins to an existing SQL server. There are several reasons just to move or copy the database logins. The most common reason would be to better maintain each SQL server instance especially if the server architecture calls for the same roles on each instance. Also, one other benefit of using this task is to make sure the SIDs are all the same in each instance which solves the issue of manually creating the logins on each instance but having different SIDs.

Implementation

As with the previous article, a blank package will be needed to complete this task so create a new package and give it an intuitive name. Find the Transfer Logins Task in the toolbox and drag it into the work area.

Double click the component to open the configuration window. Next click on the Logins tab to see what needs to be configured.

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.

Like the previous article in the Connections table, create a new SourceConnection and a DestinationConnection to the appropriate SQL server instances. From here, there are two available options on what logins to transfer, one being transfer selected logins and the other being transfer all logins for a particular database. By default, the SelectedLogins option is selected. For this article, leave the option as default. Since the SelectedLogins option is chosen, click in the LoginsList box to open up a configuration window to select which logins need to be transferred.

Please note that when selecting the logins manually or selecting all logins from a database that the database the logins belongs to needs to be present on the destination SQL server instance. Otherwise an error will occur when the package is executed. Once the logins are selected, click OK. In the Options table the two available options is what to do if the login object already exists in the destination SQL server instance and whether to copy the SIDs of the login object. Since there is a need to make sure the logins are the same from one instance to another, the IfObjectExists option is set to Overwrite and the CopySids option is set to True.

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

Once this is completed, click OK.

Testing the Package

It is time to test the package since everything to transfer logins is contained in this one component. Execute the package to transfer the selected logins.

When the package has completed running, open up SQL Management Studio to double check that the logins were transferred successfully.

What have we learned?

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

How to configure a Transfer Logins Task.
The different uses for transferring database logins.
What the different transfer options are available whether it is Selected Logins or All Selected Database Logins.

Attachments