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.
- The contained databases
- The SSIS Transfer Login Task
- The Database Migration Wizard
- 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:
| 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. |
| 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. |
| 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. |
| This option is recommended for SQL 2008 and older versions because it retrieves the passwords. |
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
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023