搜尋本站文章

2017-06-21

[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

沒有留言:

張貼留言