若資料庫擁有者(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
沒有留言:
張貼留言