如何查詢伺服器角色中有哪些成員?

本文將介紹如何查詢伺服器角色中有哪些成員。

【情境描述】

論壇上有網友在討論想要利用 administrator 帳戶登入 SQL Server ,進行建立或管理維護計畫的動作時,發現在物件總管中看不到維護計畫的項目(如下圖)。

image

image

【問題發生原因】

假設您在安裝 SQL Server 過程當中沒有指定 Administrator 帳戶為 Database Engine 管理員,或是在安裝後沒有把 Administrator 加入 sysadmin 這個固定伺服器角色,導致 administrator 帳戶沒有建立和管理為維護計畫所需 sysadmin 固定伺服器角色的權限,因而在物件總管中看不到維護計畫的項目。

【解決方式】

您可以在物件總管中經過【執行個體 > 安全性 > 伺服器角色 > sysadmin】 來查看 sysadmin 角色的成員(如下圖)。

image

或是以利用下列的 T-SQL 來查詢有哪些登入識別名稱屬於 sysadmin 固定伺服器角色。

   1: select b.name
   2: from sys.server_role_members a
   3: join sys.server_principals b
   4: on a.member_principal_id = b.principal_id
   5: where a.role_principal_id = 3

另外您也可以利用下列 T-SQL 來達到相同的目的:

SELECT c.name
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = 'R' AND a.name ='sysadmin'
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id

若 Administrator 不在上述 T-SQL 的執行結果集當中,表示 Administrator 不屬於 sysadmin 伺服器角色,因此登入到 SQL Server 中不會出現沒有權限使用的維護計畫項目,您只要將 Administrator 帳戶加入 sysadmin 角色即可。

【延伸內容】

若您想查詢其他 SQL Server 固定伺服器角色,可以參考下列的 T-SQL:

   1: if object_id ('usp_get_server_role_members') is not null
   2:     drop procedure usp_get_server_role_members
   3: go
   4:  
   5: create procedure usp_get_server_role_members (@server_role_principal_id int)
   6: as
   7: begin
   8:     set nocount on
   9:  
  10:     select b.name
  11:     from sys.server_role_members a
  12:     join sys.server_principals b
  13:     on a.member_principal_id = b.principal_id
  14:     where a.role_principal_id = @server_role_principal_id
  15: end
  16:  
  17: go
  18:  
  19:  
  20: exec usp_get_server_role_members 2 -- 查詢屬於public角色的成員
  21: exec usp_get_server_role_members 3 -- 查詢屬於sysadmin角色的成員
  22: exec usp_get_server_role_members 4 -- 查詢屬於securityadmin角色的成員
  23: exec usp_get_server_role_members 5 -- 查詢屬於serveradmin角色的成員
  24: exec usp_get_server_role_members 6 -- 查詢屬於setupadmin角色的成員
  25: exec usp_get_server_role_members 7 -- 查詢屬於processadmin角色的成員
  26: exec usp_get_server_role_members 8 -- 查詢屬於diskadmin角色的成員
  27: exec usp_get_server_role_members 9 -- 查詢屬於dbcreator角色的成員
  28: exec usp_get_server_role_members 10 -- 查詢屬於bulkadmin角色的成員