教學課程：使用 T-SQL DDL 語法將 SQL Server 實例中的 Windows 使用者和群組移轉至 Azure SQL 受控實例

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

本文會引導您完成使用 T-SQL 語法將 SQL Server 中的內部部署 Windows 使用者和群組移轉至 Azure SQL 受控實例的程式。

在本教學課程中，您會了解如何：

• 建立 SQL Server 的登入
• 建立要移轉的測試資料庫
• 建立登入、使用者和角色
• 將資料庫備份和還原至 SQL 受控實例 （MI）
• 使用 ALTER USER 語法手動將使用者遷移至 MI
• 使用新的對應使用者測試驗證

必要條件

若要完成本教學課程，適用下列必要條件：

• Windows 網域會與 Azure Active Directory （Azure AD） 同盟。
• 存取 Active Directory 以建立使用者/群組。
• 內部部署環境中的現有 SQL Server。
• 現有的 SQL 受控實例。 請參閱 快速入門：建立 SQL 受控實例 。
  • sysadminSQL 受控實例中的 ，必須用來建立 Azure AD 登入。
• 建立 SQL 受控實例 的 Azure AD 管理員。
• 您可以連線到網路內的 SQL 受控實例。 如需詳細資訊，請參閱下列文章：
  • 將您的應用程式連線到 Azure SQL 受控執行個體
  • 快速入門：設定從內部部署連線至 Azure SQL 受控實例的點對站連線
  • 在 Azure SQL 受控實例中設定公用端點

T-SQL DDL 語法

以下是用來支援使用 Azure AD 驗證將 Windows 使用者和群組從 SQL Server 實例移轉至 SQL 受控實例的 T-SQL DDL 語法。

-- 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
將使用者重新對應至 Azure AD 登入

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 受控實例的 Azure AD 系統管理員帳戶登入您的 SQL 受控實例。 然後使用下列語法，在 SQL 受控實例中建立您的 Azure AD 登入。 如需詳細資訊，請參閱 教學課程：使用 Azure AD 伺服器主體的 Azure SQL Database 中的 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 語法將內部部署使用者對應至 Azure AD 登入。

   /** 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 語法將內部部署群組對應至 Azure AD 登入。

   /** 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 部分：測試 Azure AD 使用者或群組驗證

使用先前使用 ALTER USER 語法對應至 Azure AD 登入的使用者，測試對 SQL 受控實例進行驗證。

1. 使用您的 Azure SQL 受控實例訂用帳戶登入同盟 VM aadsqlmi\testUser1

2. 使用 SQL Server Management Studio （SSMS），使用 Active Directory 整合式 驗證登入 SQL 受控實例，連線到資料庫 migration 。

   1. 您也可以使用 testUser1@aadsqlmi.net 認證搭配 SSMS 選項 Active Directory – Universal 搭配 MFA 支援 來登入。 不過，在此情況下，您無法使用單一登入機制，而且必須輸入密碼。 您不需要使用同盟 VM 來登入 SQL 受控實例。

3. 作為角色成員 SELECT 的一部分，您可以從資料表中 test 選取

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

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

1. 使用您的 Azure SQL 受控實例訂用帳戶登入同盟 VM aadsqlmi\testGroupUser

2. 使用 SSMS 搭配 Active Directory 整合式 驗證，連線到 Azure SQL 受控實例伺服器和資料庫 migration

   1. 您也可以使用 testGroupUser@aadsqlmi.net 認證搭配 SSMS 選項 Active Directory – Universal 搭配 MFA 支援 來登入。 不過，在此情況下，您無法使用單一登入機制，而且必須輸入密碼。 您不需要使用同盟 VM 來登入 SQL 受控實例。

3. 身為角色的 db_owner 一部分，您可以建立新的資料表。

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

注意

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


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 受控實例