数据库迁移登录名_如何将数据库的登录名迁移到其他服务器

数据库迁移登录名

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
   SSIS转移登录任务
3. The Database Migration Wizard
   数据库迁移向导
4. The Microsoft scripts
   Microsoft脚本

包含的数据库 (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.

SQL Server 2008和更高版本引入了“包含的数据库”功能。 使用包含的数据库，您无需登录即可连接到数据库。 您可以使用数据库用户直接连接到数据库。

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.

默认情况下，此功能未启用。 在SQL Server Management Studio（SSMS）中，右键单击“服务器属性”，转到“高级”页面，然后将“ 启用包含的数据库”设置为True 。

Figure 1 图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

图2

Once enabled, you can create new users.

启用后，您可以创建新用户。

Figure 3

图3

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

带有密码SQL用户选项使您无需登录即可拥有独立的数据库用户。

Figure 4

图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.

此功能使用SQL帐户，因此，如果未配置，请在“服务器属性”中的源服务器和目标服务器的“安全性”页面中选择“ SQL Server和Windows身份验证”模式。

Figure 5

图5

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

此功能要求您在源服务器和目标服务器上重新启动SQL Server服务。

Figure 6

图6

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

为了测试此功能，请备份您的源数据库。

Figure 7

图7

Restore the database backup at the destination Server.

在目标服务器上还原数据库备份。

Figure 8

图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.

现在，尝试使用在源数据库中创建的用户进行连接。 为此，选择“ SQL Server身份验证”，提供“登录名”和“密码”，然后按“选项”按钮。

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

为了进行测试，请尝试使用SQL Server数据库用户连接到SQL Server。

Figure 9

图9

Connect using the SQL Server database user information.

使用SQL Server数据库用户信息进行连接。

Figure 10

图10

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

手动编写要连接的数据库名称，返回登录选项卡，然后按connect。

Figure 11

图11

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

如果一切正常，您将可以使用SQL Server数据库用户访问数据库。

Figure 12

图12

SSIS转移登录任务 ( 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.

第二种选择是在SSIS中使用任务。 如果您已经有一个数据库并且使用包含的数据库为时已晚，这可能会很有用并建议使用。 另外，当您需要自定义数据库迁移时，它非常有用。 如果您没有任何SSIS经验，请不要担心。 本教程适用于新手。

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

为了开始，请打开SQL Server数据工具。

Figure 13

图13

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

转到“归档新项目”，然后选择“ Integration Services项目”。

Figure 14

图14

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

将“传输登录名”任务拖放到图形窗格中。

Figure 15

图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.

编写源连接和目标连接，然后单击SelectedLogins以选择LoginToTransfer选项。 您可以删除登录名（如果存在），忽略它们或跳过它们。 您也可以从源数据库复制Sid。

Figure 16

图16

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

检查要迁移到目标数据库的登录名。

Figure 17

图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.

运行程序包。 如果一切正常，您将复制登录名。 默认情况下，禁用SQL Server登录名并更改密码。 您将需要使用T-SQL语句启用和设置新密码。

Figure 18

图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.

复制数据库向导是复制登录名的替代方法，与SSIS传输登录任务非常相似。 主要优点是它可以复制数据库和登录名。 缺点是它比SSIS选项的可定制性差。

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

为了在SSMS中启动，请右键单击要迁移的数据库，然后选择“任务”>“复制数据库”

Figure 19

图19

A wizard will be displayed. Press next.

将显示一个向导。 按下一步。

Figure 20

图20

Specify the Source Server.

指定源服务器。

Figure 21

图21

Specify the Destination Server.

指定目标服务器。

Figure 22

图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.

您可以使用分离和附加选项（这是最快的选项）来复制数据库，也可以使用速度较慢的“ SQL管理对象”方法来复制数据库，但是它会使源数据库在所有迁移过程中保持联机状态。

Figure 23

图23

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

您可以使用此选项复制或移动一个或多个数据库。

Figure 24

图24

You can also specify the Folder destination.

您还可以指定文件夹目的地。

Figure 25

图25

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

按用户选择的登录名以选择要复制的登录名。

Figure 26

图26

You can copy all logins or select them individually.

您可以复制所有登录名或单独选择它们。

Figure 27

图27

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

该向导将创建一个程序包，相关的事件将存储在Windows事件日志中。

Figure 28

图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.

您可以立即运行复制数据库向导，也可以计划在特定的日期和时间运行。 默认情况下，将使用SQL Server代理服务帐户执行作业。 此服务帐户在服务器之间没有特权。 您将需要一个代理帐户才能使其正常工作。 要创建代理帐户，您将需要一个凭据和一个代理帐户。 有关SSIS代理帐户的更多信息，请参考此链接。

Figure 29

图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

图30

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

如果检查作业步骤，您会注意到，复制数据库向导在内部会在SSIS中创建一个程序包。

Figure 31

图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.

默认情况下，不会像SSIS传输登录任务一样禁用复制的登录名，但是SQL Server登录名需要新密码才能登录。

Figure 32

图32

Microsoft脚本 ( 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.

最后一个选项根本不友好，但是对于SQL Server 2008或更早版本，它是一个很好的选择。 如果您使用的是SQL 2012或更高版本，则最好使用其他替代方法。

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.

对于SQL 2008或更早版本，此替代方法检索密码，但是在SQL 2012或更高版本中，由于改进了加密算法，因此无法检索密码。

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:

在本文中，我们展示了4种迁移数据的方法。 下表恢复了何时使用每种方法：

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.
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.
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.
The Microsoft scripts	This option is recommended for SQL 2008 and older versions because it retrieves the passwords.

包含的数据库	如果您主要使用多个SQL Server登录名，并且需要多次迁移或分发数据库，​​则此解决方案是避免迁移问题的最佳方法。
SSIS任务	如果“复制迁移向导”不能满足您的需要，并且您需要自定义解决方案，则建议使用此解决方案。 如果只有Windows登录名，则此选项非常简单。 如果您具有SQL Server登录名，则将需要启用帐户并重置密码。
复制数据库向导	此解决方案是一个很好的解决方案，尤其是在只有Windows登录名的情况下。 如果您没有任何SSIS经验，则建议使用此选项。
Microsoft脚本	对于SQL 2008和更早版本，建议使用此选项，因为它会检索密码。

翻译自: https://www.sqlshack.com/migrate-logins-database-different-server/

数据库迁移登录名