教程：使用 T-SQL DDL 语法将 SQL Server 实例中的 Windows 用户和组迁移到 Azure SQL 托管实例

适用于：Azure SQL 托管实例

本文逐步讲解如何使用 T-SQL 语法将 SQL Server 中的本地 Windows 用户和组迁移到 Azure SQL 托管实例。

在本教程中，你将了解如何执行以下操作：

• 创建 SQL Server 的登录名
• 创建要迁移的测试数据库
• 创建登录名、用户和角色
• 将数据库备份和还原到 SQL 托管实例 (MI)
• 使用 ALTER USER 句法将用户手动迁移到 MI
• 使用新的映射用户测试身份验证

注意

Microsoft Entra ID 以前称为 Azure Active Directory (Azure AD)。

先决条件

若要完成本教程，需要符合以下先决条件：

• Windows 域与 Microsoft Entra ID 联合。
• 可访问 Active Directory 来创建用户/组。
• 本地环境中有一个现有的 SQL Server。
• 现有的 SQL 托管实例。 请参阅快速入门：创建 SQL 托管实例。
  • 必须使用 SQL 托管实例中的 sysadmin 来创建 Microsoft Entra 登录名。
• 为 SQL 托管实例创建 Microsoft Entra 管理员。
• 可以连接到网络中的 SQL 托管实例。 有关更多信息，请参阅以下文章：
  • 将应用程序连接到 Azure SQL 托管实例
  • 快速入门：配置从本地到 Azure SQL 托管实例的点到站点连接
  • 在 Azure SQL 托管实例中配置公共终结点

T-SQL DDL 语法

以下 T-SQL DDL 语法用于支持使用 Microsoft Entra 身份验证将 Windows 用户和组从 SQL Server 实例迁移到 SQL 托管实例。

-- For individual Windows users with logins
ALTER USER [domainName\userName] WITH LOGIN = [loginName@domainName.com];

--For individual groups with logins
ALTER USER [domainName\groupName] WITH LOGIN=[groupName]

参数

domainName
指定用户的域名。

userName
指定在数据库中标识的用户的名称。

= loginName@domainName.com
将用户重映射到 Microsoft Entra 登录名

groupName
指定在数据库中标识的组的名称。

第 1 部分：在 SQL Server 中为 Windows 用户和组创建登录名

重要

以下语法在 SQL Server 中创建用户和组登录名。 在执行以下语法之前，需确保该用户和组在 Active Directory (AD) 中存在。

用户：testUser1、testGroupUser
组：要迁移的 testGroupUser 需属于 AD 中的迁移组

以下示例在 SQL Server 中为域 aadsqlmi 下名为 testUser1 的帐户创建一个登录名。

-- Sign into SQL Server as a sysadmin or a user that can create logins and databases

use master;  
go

-- Create Windows login
create login [aadsqlmi\testUser1] from windows;
go;

/** Create a Windows group login which contains one user [aadsqlmi\testGroupUser].
testGroupUser will need to be added to the migration group in Active Directory
**/
create login [aadsqlmi\migration] from windows;
go;


-- Check logins were created
select * from sys.server_principals;
go;

创建一个数据库用于此次测试。

-- Create a database called [migration]
create database migration
go

第 2 部分：创建 Windows 用户和组，然后添加角色和权限

使用以下语法创建测试用户。

use migration;  
go

-- Create Windows user [aadsqlmi\testUser1] with login
create user [aadsqlmi\testUser1] from login [aadsqlmi\testUser1];
go

检查用户权限：

-- Check the user in the Metadata
select * from sys.database_principals;
go

-- Display the permissions – should only have CONNECT permissions
select user_name(grantee_principal_id), * from sys.database_permissions;
go

创建一个角色，并将测试用户分配到此角色：

-- Create a role with some permissions and assign the user to the role
create role UserMigrationRole;
go

grant CONNECT, SELECT, View DATABASE STATE, VIEW DEFINITION to UserMigrationRole;
go

alter role UserMigrationRole add member [aadsqlmi\testUser1];
go

使用以下查询显示分配给特定角色的用户名：

-- Display user name assigned to a specific role
SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
 FROM sys.database_role_members AS DRM
 RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
 LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

使用以下语法创建一个组。 然后将该组添加到角色 db_owner。

-- Create Windows group
create user [aadsqlmi\migration] from login [aadsqlmi\migration];
go

-- ADD 'db_owner' role to this group
sp_addrolemember 'db_owner', 'aadsqlmi\migration';
go

--Check the db_owner role for 'aadsqlmi\migration' group
select is_rolemember('db_owner', 'aadsqlmi\migration')
go
-- Output  ( 1 means YES)

使用以下语法创建一个测试表并添加一些数据：

-- Create a table and add data
create table test ( a int, b int);
go

insert into test values (1,10)
go

-- Check the table values
select * from test;
go

第 3 部分：将各个用户数据库备份和还原到 SQL 托管实例

参考使用备份和还原复制数据库一文创建迁移数据库的备份，或使用以下语法：

use master;
go
backup database migration to disk = 'C:\Migration\migration.bak';
go

遵循快速入门：将数据库还原到 SQL 托管实例。

第 4 部分：将用户迁移到 SQL 托管实例

执行 ALTER USER 命令以在 SQL 托管实例上完成迁移过程。

1. 使用 SQL 托管实例的 Microsoft Entra 管理员帐户登录 SQL 托管实例。 然后，使用以下语法在 SQL 托管实例中创建 Microsoft Entra 登录名。 有关详细信息，请参阅教程：使用 Microsoft Entra 服务器主体（登录名）的 Azure SQL 数据库中的 SQL 托管实例安全性。

   use master
   go
   
   -- Create login for AAD user [testUser1@aadsqlmi.net]
   create login [testUser1@aadsqlmi.net] from external provider
   go
   
   -- Create login for the Azure AD group [migration]. This group contains one user [testGroupUser@aadsqlmi.net]
   create login [migration] from external provider
   go
   
   --Check the two new logins
   select * from sys.server_principals
   go
   

2. 检查要迁移的数据库、表和主体是否正确。

   -- Switch to the database migration that is already restored for MI
   use migration;
   go
   
   --Check if the restored table test exist and contain a row
   select * from test;
   go
   
   -- Check that the SQL on-premises Windows user/group exists  
   select * from sys.database_principals;
   go
   -- the old user aadsqlmi\testUser1 should be there
   -- the old group aadsqlmi\migration should be there
   

3. 使用 ALTER USER 语法将本地用户映射到 Microsoft Entra 登录名。

   /** Execute the ALTER USER command to alter the Windows user [aadsqlmi\testUser1]
   to map to the Azure AD user testUser1@aadsqlmi.net
   **/
   alter user [aadsqlmi\testUser1] with login = [testUser1@aadsqlmi.net];
   go
   
   -- Check the principal
   select * from sys.database_principals;
   go
   -- New user testUser1@aadsqlmi.net should be there instead
   --Check new user permissions  - should only have CONNECT permissions
   select user_name(grantee_principal_id), * from sys.database_permissions;
   go
   
   -- Check a specific role
   -- Display Db user name assigned to a specific role
   SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
   FROM sys.database_role_members AS DRM
   RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
   LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
   WHERE DP1.type = 'R'
   ORDER BY DP1.name;
   

4. 使用 ALTER USER 语法将本地组映射到 Microsoft Entra 登录名。

   /** Execute ALTER USER command to alter the Windows group [aadsqlmi\migration]
   to the Azure AD group login [migration]
   **/
   alter user [aadsqlmi\migration] with login = [migration];
   -- old group migration is changed to Azure AD migration group
   go
   
   -- Check the principal
   select * from sys.database_principals;
   go
   
   --Check the group permission - should only have CONNECT permissions
   select user_name(grantee_principal_id), * from sys.database_permissions;
   go
   
   --Check the db_owner role for 'aadsqlmi\migration' user
   select is_rolemember('db_owner', 'migration')
   go
   -- Output 1 means 'YES'
   

第 5 部分：测试 Microsoft Entra 用户或组身份验证

通过先前使用 ALTER USER 语法映射到 Microsoft Entra 登录名的用户来测试对 SQL 托管实例的身份验证。

1. 以 aadsqlmi\testUser1 身份使用 Azure SQL 托管实例订阅登录到联合的 VM

2. 使用 SQL Server Management Studio (SSMS) 登录到使用 Active Directory 集成身份验证并要连接到数据库 migration 的 SQL 托管实例。

   1. 也可以结合 SSMS 选项“Active Directory – 通用且具有 MFA 支持”使用 testUser1@aadsqlmi.net 凭据登录。 但是，在这种情况下无法使用单一登录机制，而必须键入密码。 不需要使用联合的 VM 登录到 SQL 托管实例。

3. 作为角色成员 SELECT 的一部分，可以从 test 表中进行选择

   Select * from test  --  and see one row (1,10)
   

使用 Windows 组 migration 的成员测试对 SQL 托管实例的身份验证。 在迁移之前，用户 aadsqlmi\testGroupUser 应已添加到组 migration 中。

1. 以 aadsqlmi\testGroupUser 身份使用 Azure SQL 托管实例订阅登录到联合的 VM

2. 结合使用 SSMS 和 Active Directory 集成身份验证连接到 Azure SQL 托管实例服务器和数据库 migration

   1. 也可以结合 SSMS 选项“Active Directory – 通用且具有 MFA 支持”使用 testGroupUser@aadsqlmi.net 凭据登录。 但是，在这种情况下无法使用单一登录机制，而必须键入密码。 不需要使用联合的 VM 登录到 SQL 托管实例。

3. 作为 db_owner 角色的一部分，可以创建一个新表。

   -- Create table named 'new' with a default schema
   Create table dbo.new ( a int, b int)
   

注意

由于 Azure SQL 数据库的已知设计问题，以组成员身份执行的 create a table 语句将会失败并出现以下错误：

Msg 2760, Level 16, State 1, Line 4 The specified schema name "testGroupUser@aadsqlmi.net" either does not exist or you do not have permission to use it.

目前的解决方法是使用现有的架构创建一个表，在上例中为 <dbo.new>

后续步骤

教程：使用 DMS 将 SQL Server 脱机迁移到 Azure SQL 托管实例