This is the third article in the series of Migrating SQL Server Objects using SSDT 2017. In the second article, we learned how we can transfer the stored procedure of the master database. In this article, we will learn how to copy SQL Logins to another instance using SSDT 2017.

Demo Setup

For demonstration, I have created the following users in the Nisarg-PC\SQL01 server:

1. Apptracer
2. Nisarg Upadhyay
3. Nirali Upadhyay
4. AppUser

To create the Login, run the script:

Create SSIS Package

First, let us create a new SSIS project. Launch SQL Server Data tools (SSDT 2017) 🡪 Create New Project 🡪 Select Integration services project. The name of the project is Transfer Logins.

To transfer the SQL Logins, we are using the Transfer Login Task. Drag Transfer Login Task from SSIS toolbox and drop it on Control Flow Surface.

Now, let us configure it.

Configure Transfer Login Task

To configure, double-click on Transfer Login Task. A dialog box named Transfer Logins Task editor opens. Click on Logins to see the following configuration parameters.

1. Source and Destination connections
2. Logins that you want to transfer
3. Options

Let us configure the source and destination connection.

Configure Source and Destination Connection

To configure the source connection, click on SourceConnection and select New Connection.

In the SMO connection manager editor, specify the Nisarg-PC\SQL01 in the Server name and select windows authentication to log on to the server.

Click OK to save the connection and close the dialog box. To configure the source connection, click on SourceConnection and select New Connection.

Similarly, for destination connection, specify the Nisarg-PC\SQL02 in the Server name and select windows authentication to log on to the server.

The Connection section looks like the following image:

Select SQL Logins to transfer

Under the logins section, there are three options:

1. LoginsToTransfer: This option is used to select the SQL logins that you want to transfer. This option has the following options:
   1. AllLogins: You can use this option if you want to transfer all logins from all databases
   2. AllLoginsFromSelectedDatabases: You can use this option to transfer all SQL Logins from a specific database
   3. SelectedLogins: you can use this option if you want to transfer selected logins
2. LoginsList: This option enables when you select SelectedLogins from LoginsToTransfer drop-down box. When you click on LoginsList, a dialog box named Select Login opens. You can choose the appropriate Login from the list
3. DatabaseList: This option enables when you select AllLoginsFromSelectedDatabases from LoginsToTransfer drop-down box. When you click on DatabaseList, a dialog box named Select Databases opens. You can choose the appropriate database from the list

In our demo, we are copying the selected logins, so choose SelectedLogins.

It enables the LoginsList collection. Click on it. Another dialog box opens that contains all users of Nisarg-PC\SQL01.

Select users and click OK to close the dialog box.

Configure Other Options

Under the Options section, we get the following options:

1. IfObjectExists: You choose the action when the Login already exists. You can select any of the following:
   1. FailTask: This will terminate the execution and report the status of the task as failed
   2. Overwrite: The SQL Login will be overwritten
   3. Skip: Do not change anything
2. CopySIDs: Each Login is associated with a sid. If you want to copy the sid with the Login, select True. If you do not want to copy the SIDs select False

In our demo, we will skip the transfer of the users created on Nisarg-PC\SQL01; therefore, select Skip from IfObjectExists drop-down box. We want to copy the SIDs, so select True from CopySIDs drop-down box.

The Transfer Logins task editor looks like the following image:

Click OK to save the configuration and close the dialog box.

Let us configure the Notify Operator task, so we receive an email when the Transfer Login Task completes.

Configure Notify Operator Task

First, drag the Notify operator task from the SSIS toolbox, drop it in the Control flow surface, rename it to Send an email, and double-click on it to configure.

First, we must configure the connection between the server on which the operator has been created. To do that, click on the New in Notify Operator task dialog box. Another dialog box, Connect Properties, opens. Specify the appropriate values of the following parameters.

1. Connection Name: Specify the desired connection name. In our demo, I have named it as SQLConnection
2. Server Name: Specify the hostname on which the operator has been created. On the Nisarg-PC\SQL01 server, I have created an operator named DBASupport
3. Logon Information: Specify the authentication method. In this demo, we are using windows authentication

Click OK to close the dialog box.

Close the dialog box to save the connection properties.

Specify the email subject in the Notification message subject textbox. In our demo, the subject line is the Status of Migration of SQL Logins.

Specify the email body in the notification message body. In our demo, the email body is following:

Hello DBASupport.,
The SQL Logins have been transferred successfully.

Finally, the Notify operator task looks like the following image:

The email must be sent after all jobs are migrated successfully. To do that, we must connect both tasks using a connector. The package looks like the following:

The Transfer SQL Logins Task has been configured successfully.

Summary

In this article of this series, we learned about the Transfer SQL Login Task of SSDT 2017. I have explained how we can configure it to transfer SQL Logins between Nisarg-PC\SQL01 and Nisarg-PC\SQL02. In the next article, we will learn about the transfer database task and how to use it to copy the entire database between two instances of SQL Server and create a package using the SSDT 2017.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022