前言
在前一篇文章中,介紹了在「自主資料庫」上撰寫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使用者」之密碼,並討論了相關的注意事項等主題。