前言
在前一篇文章中，介紹了在「自主資料庫」上撰寫Transact-SQL陳述式、定序與自主資料庫之間關係討論、備份自主資料庫、還原自主資料庫等主題。
在本期文章內，我們將討論資料庫移轉為「自主資料庫」、查詢資料庫內是否有無法移轉的物件、自主資料庫的安全性考量、變更「自主資料庫」內的「有密碼的SQL使用者」之密碼等主題。
實作練習四：將資料庫移轉為「自主資料庫」
準備工作：
1. 使用範例資料庫：Northwind。
2. 執行以下的範例程式碼，在資料庫：Northwind上，建立新的SQL登入帳戶。
USE [master]
GO
-- 01_建立SQL登入帳戶：nw01
CREATE LOGIN [nw01]
WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- 02_可以登入到資料庫：Northwind
USE [Northwind]
GO
CREATE USER [nw01]
FOR LOGIN [nw01]
GO
-- 03_隸屬於資料庫角色：db_datareader的成員
USE [Northwind]
GO
ALTER ROLE [db_datareader]
ADD MEMBER [nw01]
GO
範例程式碼18：建立SQL登入帳戶，存取資料庫Northwind
3. 建立一個Windows帳戶：ws01，組態其可以登入到資料庫：Northwind內，隸屬於資料庫角色：db_datareader的成員。
任務一：查詢資料庫內是否有無法移轉的物件
步驟01. 使用動態管理檢視：sys.dm_db_uncontained_entities，可以用來顯示資料庫內有哪些物件是屬於非自主的，也就是為跨越「自主資料庫」中的資料庫界限之物件。
但要注意的是：如果模組多次跨越資料庫界限，只會回報其第一次已發現的跨越物件，請參考以下的範例程式碼以及下圖所示：
USE Northwind
GO
SELECT OBJECT_NAME(major_id) N'物件名稱', class_desc N'類別描述', statement_type N'陳述式類型', feature_name N'功能名稱', feature_type_name N'功能類型'
FROM sys.dm_db_uncontained_entities
GO
範例程式碼19：查詢資料庫內是否有無法移轉的物件
圖26：查詢資料庫內是否有無法移轉的物件
在圖26中，在「類別描述」部分，有兩筆資料是：DATABASE_PRINCIPAL，也就是資料庫主體。若是對範例資料庫：AdventureWorks2012執行動態管理檢視：sys.dm_db_uncontained_entities，請參考下圖所示：
圖27：對範例資料庫：AdventureWorks2012查詢是否有無法移轉的物件
在圖27中，可以觀察到在範例資料庫：AdventureWorks2012上，有幾項功能是無法移轉到「自主資料庫」，包含有：有三個資料行與全文檢索索引有關，以及有一個「計算資料行(Computed Column)」使用到執行個體層級的函數。
步驟02. 查詢有哪些登入帳戶可以登入到目標資料庫內，並且是沒有被停用的，請參考以下的範例程式碼以及下圖所示：
USE Northwind
GO
SELECT sp.name N'登入帳戶', dp.name N'資料庫使用者', sp.type_desc N'主體類型', dp.authentication_type_desc N'驗證類型',
sp.is_disabled N'是否已停用', sp.create_date N'建立日期', sp.modify_date N'修改日期'
FROM sys.database_principals dp INNER JOIN sys.server_principals sp
ON dp.sid=sp.sid
WHERE sp.is_disabled = 0
GO
範例程式碼20：查詢有哪些登入帳戶可以登入到目標資料庫內，並且是沒有被停用的
圖28：查詢有哪些登入帳戶可以登入到目標資料庫內，並且是沒有被停用的
在圖28中，可以觀察到有三個登入帳戶，分別是：兩個SQL驗證帳戶，以及一個Windows驗證帳戶。
任務二：移轉資料庫為自主資料庫
步驟01. 執行SSMS管理工具，使用「物件總管」，點選資料庫：Northwind，滑鼠右鍵，選擇「屬性」。
步驟02. 在「資料庫屬性」視窗，點選左邊窗格的「選項」頁籤，在右邊窗格，在「內含項目類型」，下拉選擇：部分，點選「確定」。或是使用以下的範例程式碼來變更資料庫的屬性，請參考下圖所示：
USE [master]
GO
ALTER DATABASE [Northwind]
SET CONTAINMENT = PARTIAL
GO
範例程式碼21：變更為「自主資料庫」，設定CONTAINMENT = PARTIAL
圖29：變更為「自主資料庫」，設定CONTAINMENT = PARTIAL
在變更為「自主資料庫」時，請先確認沒有任何登入連線到此資料庫上，否則會導致無法變更其屬性。
步驟03. 使用系統預存程序：sp_migrate_user_to_contained，將原本SQL登入帳戶的資料庫使用者，移轉為具有密碼的「自主資料庫」使用者。藉由這個系統預存程序，可以用來移除與「執行個體」的相依性。請參考以下的範例程式碼：
USE Northwind
GO
EXEC sp_migrate_user_to_contained
@username = N'nw01',
@rename = N'keep_name',
@disable_login = N'disable_login'
GO
範例程式碼22：將SQL登入帳戶的資料庫使用者，移轉為具有密碼的「自主資料庫」使用者
在範例程式碼22中，我們將SQL登入帳戶：nw01移轉為具有密碼的「自主資料庫」使用者，並且停用了「執行個體」上的SQL登入帳戶。這個系統預存程序也會將原本SQL登入帳戶的密碼，一併移轉過去。以下是對各項引數的說明：
- @username：這是指在「自主資料庫」內SQL登入帳戶的資料庫使用者。
- @rename：這是用於當登入名稱與資料庫使用者的名稱不同時，可以選擇是要以哪一個名稱為「自主資料庫」使用者的名稱。有兩個參數值可以使用，一個是：「copy_login_name」，這表示保留登入名稱；另外一個是：「keep_name」，這表示保留資料庫使用者的名稱。
- @disablelogin：這是用來指定原本的SQL登入帳戶是要停用，還是說可以繼續使用。有兩個參數可以使用，分別是：「disable_login」以及「do_not_disable_login」。若設定為「disable_login」後，就僅能以具有密碼的「自主資料庫」使用者的方式來登入到此「自主資料庫」。
圖30：檢視SQL登入帳戶是否已經完成移轉
使用範例程式碼20，比較圖28與30中，可以觀察「資料庫使用者」：nw01，已經移轉為具有密碼的「自主資料庫」使用者。
在使用系統預存程序：sp_migrate_user_to_contained上，有以下的注意事項：
- 此預存程序僅能使用在「自主資料庫」內。
- 無法轉換內建的資料庫使用者，例如：dbo和guest。
- 使用者不能使用在已簽署的預存程序之EXECUTE AS子句中指定。
- 使用者不能擁有包含EXECUTE AS OWNER子句的預存程序。
若你希望以「自主資料庫」為單位，將此資料庫上每一個SQL登入帳戶，一次全部移轉為具有密碼的「自主資料庫」使用者，請參考以下的範例程式碼：
DECLARE @username sysname ;
DECLARE user_cursor CURSOR
FOR
SELECT dp.name
FROM sys.database_principals AS dp JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0 AND dp.name <>'dbo';
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_migrate_user_to_contained
@username = @username,
@rename = N'keep_name',
@disablelogin = N'disable_login';
FETCH NEXT FROM user_cursor INTO @username
END
CLOSE user_cursor ;
DEALLOCATE user_cursor ;
範例程式碼23：將SQL登入帳戶的資料庫使用者，移轉為具有密碼的「自主資料庫」使用者
在範例程式碼23中，使用的條件式是：WHERE dp.authentication_type = 1 AND sp.is_disabled = 0 AND dp.name <>'dbo'。這篩選了SQL登入帳戶、帳戶尚未被停用，還加入了：不得是dbo資料庫使用者。但這在SQL Server 2012線上說明的範例，卻是沒有加入此條件式，這是會導致移轉失敗，請參考以下的錯誤訊息：
訊息 12820，層級 16，狀態 1，程序 sp_migrate_user_to_contained，行 1
sp_migrate_user_to_contained 不能搭配有密碼的使用者或不是 SQL 登入的使用者類型一起使用。
步驟04. 在「自主資料庫」內，查詢各個資料庫使用者的相關資料，請參考以下的範例程式碼：
USE Northwind
GO
SELECT sp.name N'登入帳戶', dp.name N'資料庫使用者', sp.type_desc N'主體類型', dp.authentication_type_desc N'驗證類型',
sp.is_disabled N'是否已停用', dp.create_date N'建立日期', dp.modify_date N'修改日期'
FROM sys.database_principals dp LEFT JOIN sys.server_principals sp
ON dp.sid=sp.sid
WHERE dp.name <>'dbo' AND dp.authentication_type_desc <>'NONE'
GO
範例程式碼24：在「自主資料庫」內，查詢各個資料庫使用者的相關資料
圖31：nw01僅是具有密碼的「自主資料庫」使用者，沒有SQL登入帳戶
任務三：測試登入到自主資料庫
步驟01. 使用具有密碼的「自主資料庫」使用者的方式來登入到此「自主資料庫」，請參考在實作練習一：建立「自主資料庫」內的任務四：使用具有密碼的自主資料庫之使用者來登入系統。
步驟02. 確認僅能看到資料庫：Northwind。確認具備查詢資料表的權限。
在完成前述任務後，此「自主資料庫」：Northwind，已經準備好可以移轉到其他的「執行個體」上了。
實作練習五：自主資料庫的安全性考量
建議：
- 請勿啟用資料庫訪客使用者：guest。
- 若啟用了資料庫訪客使用者：guest，則在「執行個體」層級上的各個登入帳號，都可以直接登入到此資料庫上。
任務一：建立登入帳戶與資料庫使用者
步驟01. 使用SSMS管理工具，分別在「執行個體」層級以及「自主資料庫」內建立帳戶，使用參數如下。或是執行以下的範例程式碼：
- 「執行個體」層級的登入帳戶：cu01，密碼：insP@ssw0rd。
- 「自主資料庫」內的「有密碼的SQL使用者」：cu01，密碼：P@ssw0rd。
-- 01_建立「執行個體」層級的登入帳戶：cu01
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'cu01')
DROP LOGIN [cu01]
GO
CREATE LOGIN [cu01]
WITH PASSWORD=N'insP@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- 02_建立「自主資料庫」內的「有密碼的SQL使用者」：cu01
USE [ContainedDB01]
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'cu01')
DROP USER [cu01]
GO
CREATE USER [cu01]
WITH PASSWORD=N'P@ssw0rd'
GO
範例程式碼25：登入帳戶與資料庫使用者
步驟02. 執行以下的範例程式碼，來在「自主資料庫」內找出：「登入帳戶」與「資料庫使用者」名稱相同，但是「安全性識別碼(SID)」卻是不同的帳戶資料，以及下圖所示：
USE ContainedDB01
GO
SELECT spr.name N'登入帳戶', spr.sid N'登入帳戶 SID', spr.type_desc N'登入帳戶的主體類型',
dpr.name N'資料庫使用者',dpr.sid N'資料庫使用者 SID',dpr.type_desc N'資料庫使用者的主體類型',dpr.authentication_type_desc N'驗證類型'
FROM sys.server_principals spr FULL JOIN sys.database_principals dpr
ON spr.name =dpr.name COLLATE DATABASE_DEFAULT AND spr.sid <> dpr.sid
WHERE dpr.type IN ('S','U','G') AND dpr.name NOT IN ('public','dbo','guest','INFORMATION_SCHEMA','sys')
GO
範例程式碼26：「登入帳戶」與「資料庫使用者」名稱相同，但是「安全性識別碼(SID)」卻是不同
圖32：登入帳戶與資料庫使用者的SID不同
在圖32中，可以觀察到「登入帳戶」：cu01，所使用的「安全識別碼(SID)」是：0xE0878FA...，但「資料庫使用者」：cu01，所使用的「安全識別碼(SID)」卻是：0x01050000...。這兩者是使用不同的「安全識別碼(SID)」。
任務二：分別登入到執行個體與自主資料庫
步驟01. 使用「查詢編輯器」，以「登入帳戶」：cu01身分，密碼：insP@ssw0rd，登入到「執行個體」層級，這應該是可以正確登入，但卻是無法進入到「自主資料庫」內，遭遇到以下的錯誤訊息：
訊息 916，層級 14，狀態 1，行 1
伺服器主體 "cu01" 在目前的安全性內容下無法存取資料庫 "ContainedDB01"。
步驟02. 使用「查詢編輯器」，以「登入帳戶」：cu01身分，密碼：P@ssw0rd，登入到「自主資料庫」，這應該是可以正確登入，但卻是無法進入其他資料庫，遭遇到以下的錯誤訊息：
訊息 916，層級 14，狀態 1，行 1
伺服器主體 "S-1-9-3-1968369419-1083962951-2211067300-202197932." 在目前的安全性內容下無法存取資料庫 "Northwind"。
步驟03. 以伺服器角色sysadmin成員登入系統，使用「物件總管」，設定「登入帳戶」：cu01可以登入到「自主資料庫」，但卻遭遇到以下的錯誤訊息，請參考下圖所示：
訊息 15023，層級 16，狀態 1，行 1
使用者、群組或角色 'cu01' 在目前的資料庫中已經存在。
圖33：使用者、群組或角色 'cu01' 在目前的資料庫中已經存在
任務三：變更「自主資料庫」內的「有密碼的SQL使用者」之密碼
若要變更「有密碼的SQL使用者」之密碼，需要符合具備符合以下任一項的規定：
- 具備權限：ALTER ANY USER的管理者。
- 若「有密碼的SQL使用者」要變更自身的密碼，則需輸入原先的舊密碼後才能夠變更為新的密碼。這是為了防止具有權限：IMPERSONATION的使用者自行變更密碼。
步驟01. 使用「物件總管」，以伺服器角色sysadmin成員登入系統，展開「資料庫」\「ContainedDB01」\「安全性」\「使用者」節點，選擇「資料庫使用者」：cu01，滑鼠右鍵，選擇「屬性」。
步驟02. 在「資料庫使用者」視窗，在左邊窗格，點選「一般」頁籤，在右邊窗格，在「密碼」與「確認密碼」方塊，輸入新的密碼，例如：P@ssw0rd91，點選「確定」，就可以變更此「有密碼的SQL使用者」的密碼。請參考以下的範例程式碼，以及下圖所示：
USE [ContainedDB01]
GO
ALTER USER [cu01]
WITH PASSWORD=N'P@ssw0rd91'
GO
範例程式碼27：使用具備權限：ALTER ANY USER的登入帳戶來變更「有密碼的SQL使用者」的密碼
步驟03. 使用「物件總管」，以「有密碼的SQL使用者」：cu01登入系統，展開「資料庫」\「ContainedDB01」\「安全性」\「使用者」節點，選擇「資料庫使用者」：cu01，滑鼠右鍵，選擇「屬性」。
步驟04. 在「資料庫使用者」視窗，在左邊窗格，點選「一般」頁籤，在右邊窗格，輸入以下的參數：
- 在「密碼」與「確認密碼」方塊，輸入新的密碼，例如：P@ssw0rd92。
- 勾選「指定舊密碼」，在「舊密碼」方塊，輸入原先的舊密碼：P@ssw0rd91。
- 點選「確定」，就可以變更此「有密碼的SQL使用者」的密碼。
請參考以下的範例程式碼，以及下圖所示：
USE [ContainedDB01]
GO
ALTER USER [cu01]
WITH PASSWORD=N'P@ssw0rd92' OLD_PASSWORD=N'P@ssw0rd91'
GO
範例程式碼28：「有密碼的SQL使用者」要變更自身的密碼
在範例程式碼28中，使用參數：OLD_PASSWORD，輸入原本的舊密碼，就可以讓「有密碼的SQL使用者」變更自身的密碼。
圖34：「有密碼的SQL使用者」變更自身的密碼
若是沒有輸入舊密碼，將遭遇到以下的錯誤訊息：
訊息 15151，層級 16，狀態 1，行 1
無法 改變 使用者 'cu01'，因為它不存在或您沒有權限。
圖35：無法變更資料庫使用者的密碼
注意事項
請勿在「自主資料庫」上，設定啟用AUTO_CLOSE選項。
若設定後，可能會讓SSMS管理工具，無法檢視「自主資料庫」的屬性，遭遇到以下的錯誤訊息，請參考下圖所示：
無法顯示要求的對話方塊。
資料庫 '[ContainedDB01]' 無法使用屬性 DefaultFullTextLanguageLcid。此物件可能沒有此屬性，或因為存取權限不足而無法擷取。 (Microsoft.SqlServer.Smo)
圖36：無法檢視資料庫屬性：「自主資料庫」上，設定啟用AUTO_CLOSE選項
USE [master]
GO
ALTER DATABASE ContainedDB01
SET AUTO_CLOSE OFF
GO
範例程式碼29：關閉AUTO_CLOSE選項
在範例程式碼29中，若是因故在「自主資料庫」上，設定啟用AUTO_CLOSE選項。可能需要使用此範例程式碼來停用，SSMS管理工具已經無法變更資料庫屬性了。
若要將「自主資料庫」變更回「非自主資料庫」，請先刪除「有密碼的SQL使用者」。
若要將「自主資料庫」變更回「非自主資料庫」，可能會遭遇到以下的錯誤訊息，請參考下圖所示：
訊息 33233，層級 16，狀態 1，行 1
您只能在自主資料庫中建立有密碼的使用者。
訊息 5069，層級 16，狀態 1，行 1
ALTER DATABASE 陳述式失敗。
圖37：失敗：將「自主資料庫」變更回「非自主資料庫」
這是因為在「自主資料庫」內，仍有「有密碼的SQL使用者」，請刪除此種類的資料庫使用者後，就可以變更回「非自主資料庫」，請參考以下的範例程式：
USE [master]
GO
ALTER DATABASE [ContainedDB01]
SET CONTAINMENT = NONE
GO
範例程式碼30：變更回「非自主資料庫」
「有密碼的SQL使用者」的密碼保護特性，如同於一般的SQL驗證登入帳戶。
若「有密碼的SQL使用者」所指定的密碼太簡單，不符合強式密碼原則，將遭遇到以下的錯誤訊息，以及下圖所示：
訊息 15118，層級 16，狀態 1，行 1
密碼驗證失敗。因為密碼不夠複雜而不符合 Windows 原則需求。
圖38：不符合密碼複雜度原則
若刻意輸入錯誤的密碼，產生登入失敗事件，這些記錄一樣會記錄到SQL Server錯誤記錄檔內存放，請參考下圖所示：
Login failed for user 'cu01'. 原因: 密碼不符合所提供使用者的密碼。
[資料庫: 'ContainedDB01'] [CLIENT: <local machine>]
圖39：SQL Server錯誤記錄檔會記錄輸入密碼錯誤的事件
結語
在本期文章中，介紹了將資料庫移轉為「自主資料庫」、查詢資料庫內是否有無法移轉的物件、自主資料庫的安全性考量、變更「自主資料庫」內的「有密碼的SQL使用者」之密碼，並討論了相關的注意事項等主題。
訂閱