經(jīng)常有人來(lái)問(wèn)我特定 SQL Server 資料庫(kù)裡的使用者無(wú)法刪除的問(wèn)題，這問(wèn)題其實(shí)跟 SQL Server 的安全性架構(gòu)有很大關(guān)係，解決這個(gè)問(wèn)題當(dāng)然還是瞭解觀念的重要性大於知道如何解決問(wèn)題。除了講解觀念外，本篇文章也會(huì)列出一些出問(wèn)題時(shí)的情境，方便快速解決問(wèn)題。

我先假設(shè)各位已經(jīng)知道 驗(yàn)證 (Authentication) 與 授權(quán) (Authorization) 的差別，簡(jiǎn)單的來(lái)說(shuō) 驗(yàn)證 負(fù)責(zé)辨識(shí)登入者的身份，而 授權(quán) 在於提供特定特定身份授與特定的操作權(quán)限。

在 SQL Server 裡提供了兩種驗(yàn)證模式，分別是 Windows 驗(yàn)證 (Windows Authentication) 與 混合模式驗(yàn)證 (Mixed Modes authentication)，藉以控制應(yīng)用程式連接 SQL Server 的方式。另外，SQL Server 又區(qū)分了兩種登入的類(lèi)型，分別是 Windows 登入 與 SQL Server 登入 ，在從應(yīng)用程式連接資料庫(kù)時(shí)，使用 Windows 登入可以不用輸入密碼在連線參數(shù)上，這種登入方式是比較建議的登入方法。

在設(shè)定 登入帳號(hào) 時(shí)，該帳號(hào)可以隸屬於一個(gè)以上的 伺服器角色 (Server Role)，這些在 SQL Server 中存在的伺服器角色數(shù)量是固定的，無(wú)法新增、也無(wú)法刪除，預(yù)設(shè)所有新增的「登入帳號(hào)」都會(huì)自動(dòng)歸類(lèi)在 public 這個(gè) 伺服器角色 下，這個(gè) public 角色只有允許連接 SQL Server 的權(quán)力而已，並沒(méi)有任何資料庫(kù)的的存取權(quán)限。

?

所以我們從 SSMS ( Management Studio ) 中所看到的「安全性」節(jié)點(diǎn)，其實(shí)是為於「伺服器節(jié)點(diǎn)」之下，並不隸屬於任何資料庫(kù)，這是應(yīng)用程式連接 SQL Server 時(shí)的第一個(gè)安全性關(guān)卡，應(yīng)該妥為設(shè)定才行，不應(yīng)該給予登入帳號(hào)有過(guò)大的權(quán)限，例如將該登入帳號(hào)加入到 sysadmin 伺服器角色之類(lèi)的。

因此，這裡所儲(chǔ)存的是 登入帳號(hào) (Login)，而非 資料庫(kù)使用者 (Database User)，這點(diǎn)非常重要。

當(dāng)我們?cè)谌我庖粋€(gè)資料庫(kù)中建立 資料庫(kù)使用者 時(shí)，所建立的其實(shí)是一個(gè)所謂的 安全性主體 (security principal)，他代表的是一個(gè)能夠被賦予安全性設(shè)定的對(duì)象，也就是一個(gè)可授權(quán)的對(duì)象。

這裡的 資料庫(kù)使用者 在建立時(shí)必須對(duì)應(yīng)到一個(gè) 登入帳戶 ，而在建立時(shí)可以設(shè)定任意的 使用者名稱(chēng) ，方便好記即可（一般我們會(huì)習(xí)慣設(shè)定使用者名稱(chēng)與登入名稱(chēng)一致），並且挑選出一個(gè)登入帳戶，在設(shè)定好之後我們就可以針對(duì)這個(gè) 資料庫(kù)使用者 設(shè)定各種權(quán)限相關(guān)的設(shè)定，例如：資料庫(kù)角色、安全性實(shí)體、…等等。

如下圖是透過(guò) SSMS 新增 資料庫(kù)使用者 的畫(huà)面，我看過(guò)許多人在建立資料庫(kù)使用者時(shí)，會(huì)勾選如下圖藍(lán)色框框的勾選項(xiàng)目，也就是設(shè)定該使用者「擁有特定的結(jié)構(gòu)描述」，勾選這一項(xiàng)就會(huì)導(dǎo)致日後無(wú)法刪除 資料庫(kù)使用者 的問(wèn)題，解決辦法在本文稍後會(huì)提及。

?

除了設(shè)定 SQL Server 預(yù)設(shè)的 資料庫(kù)角色 外，你也可以自行新增 資料庫(kù)角色 ，並設(shè)定該角色應(yīng)該有哪些權(quán)限能用，基於資安的 最小權(quán)限原則 ( Principle of least privilege )，我們應(yīng)該授與資料庫(kù)使用者最小的使用權(quán)限，以加強(qiáng)資料庫(kù)的存取安全性，這時(shí)我們就可以透過(guò)自訂的資料庫(kù)角色來(lái)設(shè)定適當(dāng)?shù)臋?quán)限，最後再將 資料庫(kù)使用者 加入到該 資料庫(kù)角色 即可將權(quán)限套用完成。

若要設(shè)定 資料庫(kù)使用者 或 資料庫(kù)角色 的細(xì)部權(quán)限，我們可以切換至 安全性實(shí)體 (Securables) 頁(yè)籤。所謂的 安全性實(shí)體 (Securable) 與 安全性主體 (Security Principle) 不太一樣，但中文卻被翻譯的非常相似，讀者必須特別小心。這裡的 安全性實(shí)體 (Securables) 指的是可以指派權(quán)限給特定 資料庫(kù)使用者 或 資料庫(kù)角色 的資料庫(kù)物件。

?

在你搜尋出 安全性實(shí)體 (或 資料庫(kù)物件 ) 後，就可以針對(duì)這些物件設(shè)定更細(xì)部的權(quán)限，如下圖示，我選擇了一個(gè)預(yù)存程序，下方的權(quán)限部分就又分為 改變 、 取得擁有權(quán) 、 執(zhí)行 、 控制 、 檢視定義 等權(quán)限，如果你只需給特定使用者「執(zhí)行」的權(quán)限，就只要在「執(zhí)行」權(quán)限的「授與」這一欄勾選即可。

?

接下來(lái)，在資料庫(kù)層級(jí)的安全性中，還有一個(gè)所謂的 結(jié)構(gòu)描述 (Schema) 的觀念，這是從 SQL Server 2005 開(kāi)始才有的概念，在 SQL Server 2000 以前，使用者名稱(chēng)本身就是資料庫(kù)物件的一部分，但新的版本不再有這種限制，而改用 結(jié)構(gòu)描述 (Schema) 來(lái)「擁有」這些資料庫(kù)物件。

如果由 結(jié)構(gòu)描述 (Schema) 來(lái)「擁有」這些資料庫(kù)物件，那麼應(yīng)該由誰(shuí)來(lái)「擁有」 結(jié)構(gòu)描述 呢？當(dāng)然是透過(guò) 資料庫(kù)使用者 或 資料庫(kù)角色 囉！

第 1 種無(wú)法刪除的情境：資料庫(kù)預(yù)設(shè) 結(jié)構(gòu)描述 (Schema) 的擁有者被指定了想刪除的 資料庫(kù)使用者

也就是說(shuō)，當(dāng)有特定 結(jié)構(gòu)描述 被特定 資料庫(kù)使用者 所擁有時(shí)，該 資料庫(kù)使用者 就會(huì)因?yàn)楸绘i定導(dǎo)致無(wú)法刪除，所以在刪除資料庫(kù)使用者經(jīng)常會(huì)遇到一個(gè)很常見(jiàn)的錯(cuò)誤訊息如下：

Msg 15138, Level 16, State 1, Line 2
資料庫(kù)主體在資料庫(kù)中擁有 結(jié)構(gòu)描述 且無(wú)法卸除。

那麼我們要怎樣才能一次瀏覽所有 結(jié)構(gòu)描述 (Schema) 的擁有者是誰(shuí)呢？以下這行 T-SQL 可以幫你列出

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

如果你發(fā)現(xiàn)你想刪除的 資料庫(kù)使用者 擁有了特定 結(jié)構(gòu)描述 (Schema) 的話，你可以修改特定 結(jié)構(gòu)描述 的擁有者給其他人，例如：dbo

?

移轉(zhuǎn)擁有權(quán)的 T-SQL 語(yǔ)法如下：

ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbo]

?

第 2 種無(wú)法刪除的情境：無(wú)法刪除多餘的 結(jié)構(gòu)描述 ，所以也連帶無(wú)法刪除 資料庫(kù)使用者

這問(wèn)題並不常見(jiàn)，除非開(kāi)發(fā)人員真的亂新增 結(jié)構(gòu)描述 才會(huì)這樣，否則 結(jié)構(gòu)描述 應(yīng)該是在資料庫(kù)設(shè)計(jì)階段所設(shè)計(jì)過(guò)的，況且我們只要把該 結(jié)構(gòu)描述 的擁有者改成其他 資料庫(kù)使用者 或 資料庫(kù)角色 ，就可以刪除該資料庫(kù)使用者了。

但如果因?yàn)樵O(shè)計(jì)有所改變而需要?jiǎng)h除特定 結(jié)構(gòu)描述 卻無(wú)法刪除的話，其錯(cuò)誤訊息如下：

訊息 3729，層級(jí) 16，狀態(tài) 1，行 2
無(wú)法 drop schema TESTUser，因?yàn)槲锛?'PK_Table_1' 正在參考它。

為了要?jiǎng)h除該結(jié)構(gòu)描述，你必須要先轉(zhuǎn)移這些被參考到的資料庫(kù)物件的結(jié)構(gòu)描述到另一個(gè)結(jié)構(gòu)描述裡，以下是搜尋出所有該結(jié)構(gòu)描述所用到的物件清單，這一行 T-SQL 指令可以幫你快速找出使用該結(jié)構(gòu)描述的物件：（備註：如下範(fàn)例請(qǐng)將 MySchema 修改成你的 結(jié)構(gòu)描述名稱(chēng) ）

SELECT schema_name(uid) as SCHEMA_NAME, * FROM sysobjects
WHERE schema_name(uid) = 'MySchema'

假設(shè)我們要將 MySchema.View_1 物件轉(zhuǎn)移到 dbo 結(jié)構(gòu)描述，其物件名稱(chēng)會(huì)變成 dbo.View_1 ，那麼我們可以使用以下 T-SQL 執(zhí)行轉(zhuǎn)移動(dòng)作：

ALTER SCHEMA dbo TRANSFER MySchema.View_1

?

?第 3 種無(wú)法刪除的情境：有 資料庫(kù)角色 的擁有者被指定了想刪除的 資料庫(kù)使用者

這種問(wèn)題的錯(cuò)誤訊息如下：

Msg 15421, Level 16, State 1, Line 2
資料庫(kù)主體擁有 資料庫(kù)角色 且無(wú)法卸除。

一樣只要轉(zhuǎn)移擁有者即可，以下是方便查出所有 資料庫(kù)角色 擁有者的 T-SQL 語(yǔ)法：

SELECT user_name(owning_principal_id) as OWNER_NAME, * FROM [sys].[database_principals]

以下是轉(zhuǎn)移資料庫(kù)角色擁有者的語(yǔ)法：

ALTER AUTHORIZATION ON ROLE::[testrole] TO [dbo]

?

第 4 種無(wú)法刪除的情境：有 服務(wù) 的擁有者被指定了想刪除的 資料庫(kù)使用者

這種問(wèn)題的錯(cuò)誤訊息如下：

Msg 15138, Level 16, State 1, Line 2
資料庫(kù)主體在資料庫(kù)中擁有 服務(wù) 且無(wú)法卸除。

以下是方便查出所有 服務(wù) 擁有者的 T-SQL 語(yǔ)法：

SELECT user_name(principal_id) as OWNER_NAME, * FROM [sys].[services]

?

結(jié)語(yǔ)

資料庫(kù)中還有許多其他物件都有可能因?yàn)閰⒖嫉劫Y料庫(kù)使用者而無(wú)法刪除，但熟悉了基本觀念後，應(yīng)該就會(huì)懂得融會(huì)貫通，相信日後遇到無(wú)法刪除使用者的問(wèn)題應(yīng)該也能輕鬆的自行解決。

?

?

來(lái)源： http://blog.miniasp.com/post/2011/08/20/SQL-Server-Security-and-Unable-to-delete-database-user-problem.aspx

SQL Server 安全性概論與無(wú)法刪除資料庫(kù)使用者的解決辦法