Daniel Calbimonte

How to migrate the logins of a database to a different server

December 31, 2014 by

Sometimes, it is necessary to move the database from one server to another. The problem is that usually the logins and the database users are mapped. If that is the case, when you backup a Database and restore a database in another server, the logins are lost. In this article, we will show different alternatives to solve this problem.

We will show four alternatives to solve the logins problems.

  1. The contained databases
  2. The SSIS Transfer Login Task
  3. The Database Migration Wizard
  4. The Microsoft scripts

The Contained databases

SQL Server 2008 and later versions introduced the Contained Database feature. With the Contained Databases, you do not need a login to connect to a database. You can use the Database user to connect to your database directly.

By default, this feature is not enabled. In the SQL Server Management Studio (SSMS), right click on the Server Properties, go to the Advanced page and set the Enable Contained Databases to True.


Figure 1

In the database that you want to migrate, you also need to right click on it, go to the Options page and select Partial in the Containment type property. This option will allow working with logins and contained users.


Figure 2

Once enabled, you can create new users.


Figure 3

The option SQL user with password allows you to have independent database users without login.


Figure 4

This feature uses SQL Accounts, so if not configured, in the Server Properties, select the SQL Server and Windows Authentication mode in the Security page at the source and destination Servers.


Figure 5

This feature requires you to restart the SQL Server Service at the source and destination Servers.


Figure 6

In order to test this feature, backup your source database.


Figure 7

Restore the database backup at the destination Server.


Figure 8

Now, try to connect using the user created at the source database. For this purpose, select the SQL Server Authentication, provide the Login and Password and press the Options button.

In order to test, try to connect to the SQL Server using the SQL Server database user.


Figure 9

Connect using the SQL Server database user information.


Figure 10

Write manually the Database name to connect, return to the login tab and press connect.


Figure 11

If everything is OK, you will be able to access to the database using a SQL Server Database User.


Figure 12

The SSIS Transfer Login Task

The second alternative is to use a task in SSIS. This can be useful and recommended if you already have a database and it is too late to use contained databases. In addition, it is very useful when you need to customize the Database Migration. If you do not have any SSIS experience do not worry. This tutorial is for newbies.

In order to start, open the SQL Server Data Tools.


Figure 13

Go to File New Project and select the Integration Services Project.


Figure 14

Drag and drop the Transfer Logins Task to the graph pane.


Figure 15

Write the Source and Destination Connection and click the SelectedLogins to select the LoginToTransfer option. You can drop the logins if they exist, ignore them or skip them. You can also copy the Sids from the source database.


Figure 16

Check the logins that you want to migrate to the destination database.


Figure 17

Run the package. If everything is OK, you will have the logins copied. By default, the SQL Server logins are disabled and the passwords are changed. You will need to enable and set new passwords using T-SQL Sentences.


Figure 18

The Copy Database Wizard

The copy Database Wizard is an alternative to copy logins very similar to the SSIS Transfer Login Task. The main advantage is that it copies the database and the Logins. The disadvantage is that it is less customizable than the SSIS option.

In order to start in the SSMS, right click on the database to migrate and select Tasks>Copy Database


Figure 19

A wizard will be displayed. Press next.


Figure 20

Specify the Source Server.


Figure 21

Specify the Destination Server.


Figure 22

You can copy the database, using the detach and Attach option which is the fastest option or use the SQL Management Object method which is slower, but it keeps the source database online in all the migration process.


Figure 23

You can copy or move one or multiple databases with this option.


Figure 24

You can also specify the Folder destination.


Figure 25

Press the User-selected Logins to select the logins to copy.


Figure 26

You can copy all logins or select them individually.


Figure 27

The wizard will create a package and the event related will be stored in the Windows event log.


Figure 28

You can run the copy database wizard immediately or schedule to run at a specific date and time. By default, the job will be executed using the SQL Server Agent Service Account. This service account does not have privileges between Servers. You will need a proxy account to make it work. To create a Proxy account, you will require to a credential and a Proxy account. For more information about proxy accounts for SSIS, refer to this link.


Figure 29

If everything is OK, you will be able to see the job. Make sure that the Agent Service is enabled and started.


Figure 30

If you check the job steps, you will notice that internally, the copy database wizards creates a package in SSIS.


Figure 31

By default, the logins copied are not disabled like the SSIS Transfer Login task, but the SQL Server Logins require a new password to login.


Figure 32

The Microsoft Scripts

The last option is not friendly at all, but it is a good alternative for SQL Server 2008 or older versions. If you have SQL 2012 or later versions, it is better to use the other alternatives.

For SQL 2008 or older versions, this alternative retrieves the password, but in SQL 2012 or later versions, the passwords cannot be retrieved because the encryption algorithm was improved.

The complete instructions are displayed here.

Basically, you create stored procedures, execute them, and run the scripts on the target server.

Conclusion

In this article, we show 4 ways to migrate data. The following table resumes when to use each method:

  1. The contained database
If you work with mainly with several SQL Server Logins and you need to migrate or distribute the database multiple times, this solution is the best to avoid migration problems.
  1. The SSIS task
This solution is recommended if the Copy Migration Wizard does not satisfy your needs and you need to customize your solution. If you have only Windows Logins, this option is very simple. If you have SQL Server logins, you will need to enable the accounts and reset the passwords.
  1. The Copy Database Wizard
This solution is a great one especially if you have only Windows Logins. This option is recommended if you do not have any SSIS experience.
  1. The Microsoft scripts
This option is recommended for SQL 2008 and older versions because it retrieves the passwords.
Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
Maintenance

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

168 Views