教學課程：使用 T-SQL DDL 語法將 SQL Server 執行個體中的 Windows 使用者與群組移轉至 Azure SQL 受控執行個體

發行項
2024/03/11

適用於：Azure SQL 受控執行個體

本文會引導您完成使用 T-SQL 語法將內部部署 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 受控執行個體。如需詳細資訊，請參閱下列文章：

T-SQL DDL 語法

以下是用來支援將 Windows 使用者和群組從 SQL Server 執行個體移轉至使用 Microsoft Entra 驗證 SQL 受控執行個體的 T-SQL DDL 語法。

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 的帳戶建立登入。

SQL

-- 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;

為此測試建立一個資料庫。

SQL

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

第 2 部分：建立 Windows 使用者和群組，然後新增角色和權限

使用下列語法建立測試使用者。

SQL

use migration;  
go

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

檢查使用者權限：

SQL

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

建立角色，並將測試使用者指派給此角色：

SQL

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

使用下列查詢來顯示指定特定角色的使用者名稱：

SQL

-- 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。

SQL

-- 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)

使用下列語法建立測試資料表並新增一些資料：

SQL

-- 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 受控執行個體

透過複製具有備份與還原的資料庫文章所述步驟建立移轉資料庫的備份，或使用下列語法：

SQL

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

請遵循我們的快速入門：將資料庫還原至 SQL 受控執行個體。

第 4 部分：將使用者移轉至 SQL 受控執行個體

執行 ALTER USER 命令，以完成 SQL 受控執行個體上的移轉程序。

使用適用於 SQL 受控執行個體的 Microsoft Entra 管理帳戶登入您的 SQL 受控執行個體。然後使用下列語法，在 SQL 受控執行個體中建立您的 Microsoft Entra 登入。如需詳細資訊，請參閱教學課程：使用 Microsoft Entra 伺服器主體 (登入) 的 Azure SQL 資料庫的 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

請檢查您的移轉，以取得正確的資料庫、資料表和主體。

SQL

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

使用 ALTER USER 語法將內部部署用戶對應至 Microsoft Entra 登入。

SQL

/** 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;

使用 ALTER USER 語法將內部部署群組對應至 Microsoft Entra 登入。

SQL

/** 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 受控執行個體。

使用您的 Azure SQL 受控執行個體訂用帳戶，以 aadsqlmi\testUser1 登入同盟 VM
使用 SQL Server Management Studio (SSMS)，使用 Active Directory 整合驗證登入您的 SQL 受控執行個體，連接到 migration 資料庫。
1. 您也可以使用 testUser1@aadsqlmi.net 認證搭配 SSMS 選項 Active Directory – 與 MFA 支援通用來登入。不過，在此情況下，您將無法使用單一登入機制，而且必須輸入密碼。您不需要使用同盟 VM 來登入您的 SQL 受控執行個體。
身為角色成員 SELECT，您可以從資料表 test 中進行選取

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

使用 Windows 群組 migration 的成員測試驗證至 SQL 受控執行個體。移轉之前，使用者 aadsqlmi\testGroupUser 應該已新增至群組 migration。

使用您的 Azure SQL 受控執行個體訂用帳戶，以 aadsqlmi\testGroupUser 登入同盟 VM
使用 SSMS 搭配 Active Directory 整合驗證，連線到 Azure SQL 受控執行個體伺服器和資料庫 migration
1. 您也可以使用 testGroupUser@aadsqlmi.net 認證搭配 SSMS 選項 Active Directory – 與 MFA 支援通用來登入。不過，在此情況下，您將無法使用單一登入機制，而且必須輸入密碼。您不需要使用同盟 VM 來登入您的 SQL 受控執行個體。

身為角色 db_owner，您可以建立新的資料表。

SQL

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

注意

由於 Azure SQL 資料庫的已知設計問題，以群組成員身分執行的建立資料表語句時將導致失敗，並出現下列錯誤：

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 受控執行個體

共用方式為