从 Azure SQL 数据库迁移登录、数据库角色、用户和对象级权限到 Amazon RDS for SQL Server

作者：InduTeja Aligeti、Jitendra Kumar和 Ramesh Babu Donti 日期：2024年5月24日

关键要点

在本文中，我们将展示如何使用 TSQL 将 SQL 登录、数据库角色、用户和对象级权限从 Azure SQL 数据库 迁移到 Amazon RDS for SQL Server。

SQL 登录是连接 SQL Server 实例的安全主体，包含访问权限和用户特权。迁移过程的关键步骤包括验证登录、角色和权限的存在性。需确保目标 Amazon RDS for SQL Server 数据库具备必要的用户定义对象，以及授予 dbowner 数据库角色的用户。

在这篇文章中，我们演示了如何从 Azure SQL 数据库 迁移 SQL 登录、数据库角色、用户和对象级权限到 Amazon RDS for SQL Server 的方法。

在 SQL Server 中，SQL 登录作为一个安全主体，使用户或应用程序能够连接到 SQL Server 实例。它是管理访问、权限、用户特权和监控数据库活动的机制。在数据库迁移过程中，迁移 SQL 登录至关重要，因为这些登录包含连接和与数据库交互所需的认证信息、访问权和权限。虽然原生的备份和还原操作通常足以进行登录迁移，但某些场景需要额外步骤以确保无缝迁移，特别是在从 Azure SQL 数据库迁移并使用数据传输方法如 SmartbulkCopy、AWS 数据库迁移服务AWS DMS或 BCP 分别迁移数据和架构时。

SQL Server 安全组件概述

在 SQL Server 中，SQL 登录是一个安全主体，使用户或应用程序能够连接到实例。SQL 登录与用户账户相关联以进行身份验证。SQL Server 中的身份验证涉及三个关键要素：

主体：能够请求 SQL Server 内部资源的实体。主体的访问级别依赖于其作用域。可以根据作用域将主体分为两种类型：服务器级主体和数据库级主体。可安全对象：可以授予身份验证的服务器或数据库对象，如表或端点。可安全对象根据其作用域分为服务器范围、数据库范围和架构范围。权限：定义主体对可安全对象的控制。主体可以根据分配的权限对可安全对象执行特定操作。在服务器级别，权限通过登录和服务器角色进行管理，而在数据库级别，权限通过数据库用户和角色来监管。

下表展示了 Azure SQL 数据库与 Amazon RDS for SQL Server 之间的 SQL Server 安全管理差异：

活动Amazon RDS for SQL ServerAzure SQL Database管理服务器级安全的地方SQL Server Management Studio 的对象资源管理器中的 Security 文件夹master 数据库及 Azure 门户Windows 身份验证AWS Managed Microsoft ADAzure Active Directory 身份创建登录的服务器级安全角色securityadmin 固定服务器角色loginmanager 数据库角色在 master 数据库中管理登录的命令CREATE LOGIN ALTER LOGIN DROP LOGINCREATE LOGIN ALTER LOGIN DROP LOGIN存在一些参数限制，必须连接到 master 数据库显示所有登录信息的视图sysserverprincipalssyssqllogins必须连接到 master 数据库创建数据库的服务器级角色dbcreator 固定数据库角色dbmanager 数据库角色在 master 数据库中

解决方案概述

在某些情况下，登录和用户权限未作为数据库迁移过程的一部分进行迁移，例如在使用 SmartBulkCopy、AWS DMS 或 BCP 的情况下，我们使用内置的生成脚本实用工具和 TSQL 动态脚本为每个数据库创建登录、角色、用户和对象级权限。

下图展示了将登录和数据库用户从 Azure SQL 数据库迁移到 Amazon RDS for SQL Server 的高层架构。

迁移 SQL 登录及其依赖的安全组件的步骤如下：

迁移登录并验证 Azure SQL 数据库与 Amazon RDS for SQL Server 之间的登录和用户存在性。迁移服务器角色和服务器权限并验证权限。迁移用户和数据库角色并验证用户和角色。迁移对象级权限并验证权限。

前提条件

开始前，您需要具备以下条件：

作为源的 Azure SQL 数据库作为目标的 RDS for SQL Server DB 实例单可用区或 多可用区Azure SQL 数据库与 Amazon RDS for SQL Server 之间的 连接性具有所需用户定义对象表、视图、函数和存储过程以及 dbowner 固定数据库角色的用户的目标 RDS for SQL Server 数据库具有最低权限的 Azure 登录以访问 master 以获取登录和用户信息，并确保 Azure SQL 数据库的主登录名与 RDS for SQL Server 不同

迁移登录

您可以使用 SQL Server 认证账户、Microsoft Entra 服务器主体或 受管身份 访问 Azure SQL 数据库。通过从 syslogins 获取登录信息来识别 Microsoft Entra 服务器主体 和 受管身份 并手动迁移它们。确保在 Amazon RDS for SQL Server 中以 dbowner 的身份创建 EXTERNALUSERS。由于 Amazon RDS for SQL Server 的权限限制，您无法向目标用户授予 sysadmin 权限。

要将 SQL 登录从 Azure SQL 数据库迁移到 Amazon RDS for SQL Server，您可以将创建登录的脚本与哈希密码和 SID 一起生成。但是，当使用 Azure SQL 数据库作为源时，生成的 SID 和哈希密码可能会引起转换问题，并且某些函数如 SUSERSID() 不受支持。

以下是从源 SQL Server 实例生成创建登录脚本并将其迁移到目标 RDS for SQL Server 实例的步骤：

使用主用户或具有安全管理员固定角色权限的用户连接到源 Azure SQL 与 SQL Server Management Studio (SSMS)。在 SSMS 查询编辑器中，选择 结果到网格按 CtrlD选项。

运行以下脚本以生成创建登录脚本：

sql 脚本生成创建登录 USE masterGOSET NOCOUNT ONSELECT BEGIN CREATE LOGIN QUOTENAME(sqlloginname) CASE WHEN sqllogintypedesc = SQLLOGIN THEN WITH PASSWORD = CONVERT(NVARCHAR(MAX) sqlloginpasswordhash 1) HASHED CHECKEXPIRATION = CASE WHEN sqlloginisexpirationchecked = 1 THEN ON ELSE OFF END CHECKPOLICY = CASE WHEN sqlloginispolicychecked = 1 THEN ON ELSE OFF END ELSE NOTSQLLOGIN END DEFAULTDATABASE=[ sqllogindefaultdatabasename ] DEFAULTLANGUAGE=[ sqllogindefaultlanguagename ] END AS [ Amazon RDS 登录创建脚本 ] FROM syssqllogins AS sqllogin

查询的输出提供了登录创建脚本。

复制登录创建脚本并使用 Amazon RDS 的主用户或具有“修改任何登录”服务器级可安全对象的登录执行该脚本，该用户属于 RDS for SQL Server 实例中的 processadmin 和 setupadmin 固定服务器角色。检查错误并相应地修复。验证在 Azure SQL 数据库和 RDS for SQL Server 数据库中创建的登录数量。测试登录是否正常工作，使用在 Azure SQL 数据库端用于连接的任一登录及其密码连接到 RDS for SQL Server。

成功迁移登录后，您可以继续进行服务器角色成员的迁移。

在复制输出并粘贴到目标数据库时，您需谨慎处理，因为其中包含敏感数据。

迁移服务器角色和服务器权限

在 Amazon RDS for SQL Server 中，您没有超级用户或 sysadmin 权限。主用户是权限最高的用户。用户不能在 master 数据库中创建，仅在数据库级别上创建。主用户是在创建 RDS 实例时指定的，附带用户名和密码。主用户在 RDS 中拥有最大权限，但并不具备完整的 sysadmin 权限。只有 setupadmin 和 processadmin 固定服务器角色可以由主用户授予登录。有关详细信息，请参阅 RDS for SQL Server 实例中的固定服务器角色。

在 Azure SQL 数据库中，您可以使用内置的服务器级角色在 逻辑服务器 级别管理权限，而不是直接在该级别授予权限。有关详细信息，请参阅 Azure SQL 数据库的权限管理服务器角色。

将服务器级角色和权限从 Azure SQL 数据库迁移到 RDS for SQL Server 实例的关键步骤如下：

对于每个登录，使用以下脚本识别其服务器级角色：

sqlSELECT mname AS MemberName rname AS RoleName FROM sysserverrolemembers rmJOIN syssqllogins m ON rmmemberprincipalid = mprincipalidJOIN sysserverprincipals r ON rmroleprincipalid = rprincipalid

在 Amazon RDS for SQL Server 中创建登录后，您可以在 RDS 实例上重新创建 setupadmin 和 processadmin 的服务器角色，因为不可能创建其他角色。有关 AD 用户的信息，请参考 Azure SQL Database 的权限管理服务器角色 和 Microsoft Entra 服务器主体。您可以使用以下语句将登录添加为 processadmin 和 setupadmin 固定服务器角色的成员：

sqlALTER SERVER ROLE [processadmin] ADD MEMBER [ltRDSLoginNamegt]GOALTER SERVER ROLE [setupadmin] ADD MEMBER [ltRDSLoginNamegt]GO

在亚马逊 RDS 上手动将登录添加到这些角色之前，请参阅 processadmin 和 setupadmin 固定服务器角色的权限。

迁移用户和数据库角色

要迁移用户和数据库角色，请完成以下步骤：

蘑菇加速器免费

使用主用户或具有安全管理员固定角色权限的用户连接到源 Azure SQL。右键单击数据库，选择 生成脚本。

选择 下一步。

选择 选择特定数据库对象，然后选择 用户。

选择 下一步。

选择 另存为脚本文件。

输入保存文件的位置。

选择 下一步。

上面的步骤会在提供的位置生成一个脚本。

在目标 RDS for SQL Server 数据库上运行复制的脚本。通过查询和连接系统表 sysdatabaseprincipals、databaserolemembers 和 sysdatabasepermissions 验证 Azure SQL 数据库中的用户、角色和权限数量，并确保与 RDS for SQL Server 数据库匹配。

迁移对象级权限

完成以下步骤以迁移对象级权限：

使用主用户或具有安全管理员固定角色权限的用户连接到源 Azure SQL。在 SSMS 查询编辑器中，选择 结果到网格按 CtrlD选项。

运行以下脚本以生成对象级权限创建脚本：

sqlUSE ltdatabaseNamegt 迁移服务器级权限 SELECT statedesc permissionname ON [ SCHEMANAME(SysObjschemaid) ][ OBJECTNAME(DBPmajorid) ] TO [ USERNAME(DBPgranteeprincipalid) ] AS [ 添加对象级权限的命令 ] FROM sysdatabasepermissions DBP INNER JOIN sysdatabaseprincipals DBPS ON DBPgranteeprincipalid = DBPSprincipalid INNER JOIN sysobjects SysObj ON SysObjobjectid = DBPmajoridWHERE DBPSname NOT IN (public dbo)

在目标 RDS for SQL Server 数据库上运行生成的对象级权限脚本，并进行验证。

使用 SSMS 连接到目标 Amazon RDS for SQL Server，并复制第 4 步中生成的输出脚本。验证脚本输出是否有错误。

清理

为了避免将来产生费用，完成以下步骤以移除您在测试此用例时创建的所有组件：

在 Amazon RDS 控制台中，在导航窗格中选择 数据库。选择您设置的数据库，在 操作 菜单中选择 删除。输入 “delete me” 以确认删除。

有关删除实例的更多信息，请参见 删除 DB 实例。

结论

在本文中，我们描述了如何使用 TSQL 将登录、数据库角色、用户和对象级权限迁移到 Amazon RDS for SQL Server。如果您有任何问题或建议，请留下评论。

关于作者

InduTeja Aligeti 是 AWS 的高级数据库顾问。她在 Microsoft 技术领域拥有超过 18 年的经验，专注于 SQL Server。她致力于帮助客户构建高可用性和成本效益的数据库解决方案，并将大规模 SQL Server 数据库迁移到 AWS。

Jitendra Kumar