Share via

SQL Server： 使用者定義的角色

  • 發行項

隨著 SQL Server “2012 的推出，您現在能夠建立使用者定義的伺服器角色，這可謂 SQL 管理的一大進展。

Denny Cherry

雖然我們長久以來都能夠建立使用者定義的資料庫角色來簡化授與資料庫層級權限的程序，但是執行個體層級一直都只有九個固定的伺服器角色 (如果使用 SQL Server 2000 以前的版本則為八個角色；bulkadmin 角色是在 SQL Server 2005 中推出)。 而現在透過 SQL Server 2012，我們終於能夠建立使用者定義的伺服器角色。

多年以來，不能建立使用者定義的角色一直都是 SQL 管理上的難題。 當您需要在執行個體層級授與權利給多個使用者或群組，並且想要讓這些權利保持同步時該怎麼做？ 假設您想要授與「檢視系統狀態」權利給大量使用者，好讓他們檢視開發伺服器內的封鎖資訊。 您得對每個需要此權利的使用者或網域使用者一一授與權利。

如果所有登入都是以網域登入為基礎，那麼您唯一可以採行的因應措施，是為所有需要此權利的使用者建立一個網域群組 (如果使用者不具備所有伺服器的權利，可能需要針對每台伺服器分別建立一個網域群組)。 您可以把使用者放入此群組中，建立與該網域群組對應的登入，然後授與該群組在該伺服器上的「檢視系統狀態」權利。 但是，這麼做時必須非常小心。 否則，可能會把登入權利授與過去不具該權利的使用者， 甚至還可能把權利授與您不希望其擁有該權利的使用者。

T/SQL 的角色

建立使用者定義的伺服器角色的方法有好幾種，包括 T/SQL、SQL Server Management Studio UI 和 Windows PowerShell。 若是使用 T/SQL 建立使用者定義的伺服器角色，我會使用三種不同的命令。 首先，「建立伺服器角色」陳述式可建立使用者定義的伺服器角色；其次，「更改伺服器角色」可新增使用者到該伺服器角色；最後，「授與」陳述式可將所需的權利授與該角色。

下列程式碼顯示了這三個陳述式，其中概述了建立名為「ViewServerState」的使用者定義的伺服器角色。這指出新增 了一名使用者到名為「SomeFakeLogin」的角色，並且授與該角色「檢視系統狀態」權利。 若要將此權利授與其他使用者，您只需要使用「更改伺服器角色」行將該些使用者新增至固定的伺服器角色即可：

USE [master] GO CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin] GO GRANT VIEW SERVER STATE TO [ViewServerState] GO

您使用「更改伺服器角色」從使用者定義的伺服器角色移除使用者。 並且使用「卸除成員」來取代「新增成員」語法：

ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin] GO

準備好對 SQL Server 的多個執行個體部署一或多個使用者定義的伺服器角色時，有幾個不同的選項。 您可能不會想要一次只連接一台伺服器來建立使用者定義的伺服器角色。 其中一個選項是使用 SQL Server Management Studio 功能。 這可讓您對多個執行個體執行 T/SQL 指令碼。

您也可以使用 SQL Server 的 Windows PowerShell 元件，對組織內所有 SQL Server 執行個體部署全新使用者定義的伺服器角色 (由於使用 Windows PowerShell 部署這些指令碼的方法繁多，本文在此不予討論)。

SQL Server Management Studio

您可以透過 SQL Server Management Studio UI 輕鬆執行相同的動作。 若要建立使用者定義的伺服器角色，請連接到物件總管內的執行個體。 在物件總管內巡覽至 [InstanceName] | [安全性] | [伺服器角色]。 以滑鼠右鍵按一下 [伺服器角色]，然後從內容功能表選取 [新增伺服器角色]。 當 [新增伺服器角色] 視窗開啟時，指定 [伺服器角色名稱] 和 [擁有者]，然後選取 [安全性實體]，再選取您要角色成員具備的 [權限] (請參閱 [圖 1])。

[圖 1] 指定要特定使用者具備的權利。

完成 [一般] 頁面上的欄位後，選取 [成員] 頁面 (請參閱 [圖 2]) 並指定 SQL Server 登入，這將成為此使用者定義的伺服器角色的成員。

[圖 2] 選取 [成員] 將指定哪些使用者可以存取此伺服器角色。

選取 [角色成員] 之後，選取 [成員資格] 頁面。 此使用者定義的伺服器角色將是這裡指定的伺服器角色的成員。 如果您在此畫面上指定伺服器角色，則該使用者定義的伺服器角色內的使用者也會具備該伺服器角色授與的權利。

如果您建立使用者定義的伺服器角色並使其成為 serveradmin 固定伺服器角色的成員 (請參閱 [圖 3])，該使用者定義的伺服器角色的所有成員實際上並不會是 serveradmin 固定伺服器角色的成員。 就跟處理巢狀網域群組或資料庫角色一樣，在巢狀處理角色時也必須特別小心，才不會從更高端的群組授與使用者不應具備的權利。

[圖 3] 您可以將使用者定義的角色套用至其他伺服器角色。

若要使用 T/SQL 巢狀處理伺服器角色，您仍舊必須使用「更改伺服器角色」陳述式與「新增成員」語法。 例如，若要使 ViewServerState 使用者定義的伺服器角色成為 setupadmin 固定伺服器角色的成員，您得更 setupadmin 固定伺服器角色， 然後將 ViewServerState 使用者定義的伺服器角色新增為成員：

ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState] GO

使用者定義的伺服器角色有眾多用途。 您可以授與使用者定義的伺服器角色各種執行個體權利，來簡化管理這些權利的工作。 您也可以建立初級的 DBA 角色來授與一些權利，而不授與完整的系統管理員權利。 您可以建立 AlwaysOnAdmin 群組，允許在不具備完整系統管理員權利下容錯移轉 AlwaysOn 資料庫 (必須在 SQL Server 內完成)。

使用者定義的伺服器角色有多種用途， 不僅可大幅減輕系統管理的負擔， 也有助於增加 SQL Server 執行個體的安全性，因為這些新伺服器角色將減少意外授與不當權利的情況。

DennyCherry

Denny Cherry 擁有十餘年管理 SQL Server 的經驗。 他擅長的領域包括統架構、效能微調、複寫和疑難排解。 他持有數項 SQL Server 相關的認證，包括 SQL Server 2008 的微軟高級技術專家 (Microsoft Certified Master)。他擔任 Microsoft SQL Server MVP 已有多年，並且著有無數關於 SQL Server 管理以及 SQL Server 如何與其他各種技術整合的技術文章和書籍。

相關內容