[SQL Server]:變更、移轉(ALTER AUTHORIZATION)全部資料庫的擁有者(database owner)為 sa

若資料庫擁有者(database owner)有問題時，將導致許多作業無法執行。
例如：

• 建立複寫(Replication)機制
• 建立資料庫圖表(Database diagram)
• 建立 SQL CLR等作業失敗。

會發生資料庫擁有者有所異常，可能是：

• 資料庫擁有者是屬於一個不存在、被停用的Windows使用者，例如：異機備份還原、但已刪除此Windows使用者。
• 資料庫擁有者的權限問題。

可使用：ALTER AUTHORIZATION ON DATABASE 將資料庫擁有者變更移轉為 sa。

-- 產生變更全部的資料庫擁有者為 sa：ALTER AUTHORIZATION ON DATABASE
-- 但資料庫狀態必須是 ONLINE、不是 正在還原(RESTORING)、不是 唯讀(Read-Only)

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];' 
FROM sys.databases
WHERE state =0 AND is_read_only= 0 AND name NOT IN ('master', 'model', 'tempdb', 'distribution');

系統資料庫的擁有者，依據預設值是 sa 帳戶。

---

範例：ALTER AUTHORIZATION ON DATABASE 將資料庫擁有者變更移轉為 sa

-- 01_查詢：全部資料庫的擁有者
USE master
GO
SELECT d.name N'資料庫', p.name N'資料庫擁有者', d.owner_sid N'安全性識別碼', is_read_only, state_desc
FROM sys.databases  d INNER JOIN sys.server_principals p
ON d.owner_sid = p.sid
-- WHERE state =0 AND is_read_only= 0 AND d.name NOT IN ('master', 'model', 'tempdb', 'distribution')
ORDER BY p.name;

/*
條件式說明：

state = 0：資料庫狀態是 ONLINE
is_read_only = 0：資料庫狀態不是唯讀

資料庫狀態必須是 ONLINE、不是 正在還原(RESTORING)、不是 唯讀(Read-Only)

name NOT IN ('master', 'model', 'tempdb', 'distribution')：避開系統資料庫
無法變更 master、model、tempdb 或 distribution 系統資料庫的擁有者
*/

-- 01_查詢：全部資料庫的擁有者

-- 02_產生變更全部的資料庫擁有者為 sa：ALTER AUTHORIZATION ON DATABASE
-- 但資料庫狀態必須是 ONLINE、不是 正在還原(RESTORING)、不是 唯讀(Read-Only)
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];' 
FROM sys.databases
WHERE state =0 AND is_read_only= 0 AND name NOT IN ('master', 'model', 'tempdb', 'distribution');

-- 02_產生變更全部資料庫的擁有者為 sa：ALTER AUTHORIZATION ON DATABASE



-- 03_執行先前所產生的 ALTER AUTHORIZATION ON DATABASE 陳述式



-- 04_已經變更資料庫擁有者是 sa

---

相關範例

-- 01_變更全部資料庫(System, User)的擁有者為 sa
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];' 
FROM sys.databases;

-- 02_變更全部使用者資料庫(User)的擁有者為 sa
- 不包含系統資料庫
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];' 
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'distribution');

---

無法變更資料庫(database owner)的擁有者為 sa

舉例來講：

• 資料庫狀態為 唯讀(Read-Only)
• 資料庫狀態為 正在還原(Restoring)
• 無法變更 master、model、tempdb 或 distribution 系統資料庫的擁有者
• 不存在的帳戶

(一) 若資料庫狀態為唯讀(Read-Only)

無法變更資料庫(database owner)的擁有者為 sa。
例如：
AlwaysOn 次要複本(Secondary Replica)資料庫、資料庫鏡像(Database Mirroring)的鏡像資料庫等。

錯誤訊息
訊息 3906，層級 16，狀態 1，行 1
無法更新資料庫 "DB1"，因為資料庫是唯讀的。

-- 061_資料庫狀態為唯讀(Read-Only)，無法變更資料庫(database owner)的擁有者為 sa



(二) 若資料庫狀態為正在還原(Restoring)

無法變更資料庫(database owner)的擁有者為 sa。 例如： 記錄傳送(Log Shipping)的次要資料庫(Secondary Database)

錯誤訊息
訊息 927，層級 14，狀態 2，行 3
資料庫 'DB9' 無法開啟。它目前正在還原當中。

-- 062_資料庫狀態為正在還原(Restoring)，無法變更資料庫(database owner)的擁有者為 sa



(三) 無法變更 master、model、tempdb 或 distribution 系統資料庫的擁有者

錯誤訊息
訊息 15109，層級 16，狀態 1，行 3
無法變更 master、model、tempdb 或 distribution 資料庫的擁有者。

-- 063_無法變更 master、model、tempdb 或 distribution 系統資料庫的擁有者




(四) 不存在的帳戶

錯誤訊息
訊息 15151，層級 16，狀態 1，行 3
無法 尋找 主體 'sa1111'，因為它不存在或您沒有權限。

-- 064_不存在的帳戶

---

將廢除的功能：sp_changedbowner

未來的 Microsoft SQL Server 版本將移除這項功能。
請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。
請改用 ALTER AUTHORIZATION。

舊版 sp_changedbowner 範例：

-- 01_sp_changedbowner：變更全部資料庫(System, User)的擁有者為 sa
USE master
GO
EXEC sp_MSforeachdb 'USE ? EXEC sp_changedbowner ''sa'''
GO

-- 065_sp_changedbowner：變更全部資料庫(System, User)的擁有者為 sa

---

參考文件

ALTER AUTHORIZATION (Transact-SQL)
https://technet.microsoft.com/zh-tw/library/ms187359(v=sql.110).aspx

sp_changedbowner (Transact-SQL) -- 未來的 Microsoft SQL Server 版本將移除這項功能
https://technet.microsoft.com/zh-tw/library/ms178630(v=sql.110).aspx