如何撤銷似乎“卡住”登錄的 SA 權限？
如果我在這裡沒有正確使用某些術語，請提前道歉。我不是 DBA，但我暫時被迫擔任這個角色。
我繼承了一個在 SQL Server 2016 Enterprise 上執行的應用程序——一個用於 PROD 實例，另一個用於 TEST。這些實例設置為“始終線上”配置，因此我有一個用於 PROD 的可用性組和一個用於 TEST 的可用性組。每個可用性組中都有兩台伺服器。每個實例中都有多個數據庫，但每個實例中只有一個數據庫被應用程序實際使用——其他數據庫看起來是為了測試目的而創建的。我只是提到還有其他數據庫，以防它可能以某種我不知道的方式相關。
我有一個服務帳戶的登錄名（Windows 身份驗證），並且該登錄名映射到幾個數據庫中的同名使用者。此服務帳戶是 sysadmin 角色的成員。在 IT 安全審核後，我被告知要從該帳戶中刪除 SA 權限。我認為這不會給應用程序帶來任何問題，但我當然想先刪除 TEST 實例中的權限，以便我可以確認應用程序出於某種原因不需要它們。
在 TEST 實例中，我已取消選中登錄的 sysadmin 角色，現在它只選中了“公共”框，但我發現我仍然可以作為服務帳戶登錄並訪問它之前有權訪問的所有內容。它仍然可以訪問主數據庫中的所有表（這是我所期望的，因為該帳戶已被授予數據庫中 dbo 模式的很多權限），但令我困惑的是它仍然可以查看在伺服器級別，我認為它會失去。
如果我在以服務帳戶身份登錄時執行此語句 (
SELECT * FROM fn_my_permissions(NULL, 'server');)，這是我返回的權限列表：
- 連接 SQL
- 關閉
- 創建端點
- 創建任何數據庫
- 創建可用性組
- 更改任何登錄
- 更改任何憑證
- 改變任何端點
- 更改任何連結的伺服器
- 改變任何連接
- 更改任何數據庫
- 改變資源
- 更改設置
- 更改跟踪
- 更改任何可用性組
- 管理批量操作
- 驗證伺服器
- 外部訪問組件
- 查看任何數據庫
- 查看任何定義
- 查看伺服器狀態
- 創建 DDL 事件通知
- 創建跟踪事件通知
- 更改任何事件通知
- 更改伺服器狀態
- 不安全的組裝
- 更改任何伺服器審計
- 創建伺服器角色
- 更改任何伺服器角色
- 更改任何事件會話
- 連接任何數據庫
- 模擬任何登錄
- 選擇所有使用者安全
- 控制伺服器
這些對我來說似乎是 SA 級別的權限，但我不明白它們來自哪裡，因為 sysadmin 角色不再應用於登錄。我認為他們可能已被單獨授予登錄（這對我來說似乎很奇怪，因為這不只是繞過首先擁有角色的目的嗎？）所以我嘗試撤銷它們。撤消命令似乎是成功的（“命令已成功完成”），但是當我再次執行相同的命令以檢查服務帳戶的權限時，它仍然具有所有相同的權限。
我還發現，以服務帳戶身份登錄時，它可以將 sysadmin 角色分配給自己（並將其從自身中刪除）。
其他注意事項：每次我對帳戶進行更改時，我都會小心確保我在作為可用性組一部分的兩台伺服器上進行相同的更改。我的理解是，登錄在每台伺服器上都是獨立的，即使它們映射到同一個數據庫級使用者，所以對登錄的任何更改都必須在兩台伺服器上完成。
我嘗試過的另一件事是從 PROD 實例的輔助節點上的帳戶中刪除 sysadmin 角色，只是為了查看我是否在權限“卡住”時遇到了同樣的問題，並且問題不會在那裡發生。
我覺得我在這裡遺漏了一些明顯的東西，但我不知道它是什麼。任何幫助將非常感激。
使用者在 SQL Server 中獲得權限有幾種不同的方式。除了角色成員身份（如
sysadmin固定伺服器角色），您還可GRANT以為個別權限做顯式 s。由於您使用的是 Windows 服務帳戶，因此除了直接授予登錄名的任何內容之外，還可以將權限授予域組，並且這些權限會繼承。登錄可能是域組的一部分。
在活動目錄/域方面，您的服務帳戶可能包含在 AD 組中，然後
sysadmin在您的 SQL Server 實例中授予該 AD 組。然後，即使沒有直接授予登錄sysadmin名，它仍然會從組繼承成員資格。
xp_logininfo您可以通過系統儲存過程檢查哪些組正在從 SQL Server 方面授予哪些成員資格：EXEC xp_logininfo @acctname = 'MyDomain\amtwo', @option = 'ALL';輸出看起來像這樣。請注意第一行，它顯示我的
amtwo登錄通過域組admin中的“權限路徑”（閱讀：組成員身份）獲得權限。SQLSERVER_SYSADMINaccount name type privilege mapped login name permission path ---------------- -------- --------- ------------------- ---------------------------- MyDomain\amtwo user admin MyDomain\amtwo MyDomain\SQLSERVER_SYSADMIN MyDomain\amtwo user user MyDomain\amtwo MyDomain\SomeOtherGroup在這種情況下，如果我想
sysadmin從登錄中刪除權限amtwo，我需要從SQLSERVER_SYSADMIN活動目錄端的組中刪除它。（即，通過 SQL Server 配置和權限，我無能為力。）你可能只是有
CONTROL SERVER有一個名為的權限
CONTROL SERVER與幾乎相同sysadmin，但有一些不同。CONTROL SERVER將遵守DENY強制執行更細粒度的權限（sysadmin覆蓋所有內容，包括DENY權限）。此外，有時您可能會遇到僅適用於– 的舊命令或函式，sysadmin但這種情況越來越少見。可能是設置您的服務帳戶的人同時授予了
sysadmin成員資格和CONTROL SERVER權限。檢查這一點的最佳方法是使用腳本來查詢所有權限。此查詢來自我的開源 DBA 數據庫中的一個視圖，但這裡也包含完整的查詢。
正如所寫，這將為所有使用者提供所有伺服器級別的權限。您可以簡單地在 where 子句中添加一行以進一步過濾。因為這是一個 Windows 登錄，您應該檢查直接授予登錄的權限，以及它所屬的任何組（這將是使用
xp_logininfo.-- From http://am2.co/dbadb - Licensed under BSD 2-clause SELECT LoginSid = p.sid, LoginName = p.name, LoginType = p.type_desc, DefaultDatabase = p.default_database_name, LoginIsEnabled = IIF(p.is_disabled = 0,1,0), CanLogIn = COALESCE((SELECT TOP 1 1 FROM sys.server_permissions AS cosq WHERE cosq.grantee_principal_id = p.principal_id AND cosq.type = 'COSQ' AND cosq.state IN ('G','W') AND p.is_disabled = 0 ), 0), PermissionType = perm.type, PermissionState = perm.state, PermissionSql = CONCAT(perm.state_desc, N' ', perm.permission_name, N' TO ', QUOTENAME(p.name) COLLATE Latin1_General_CI_AS_KS_WS, N';' ), DateLoginCreated = p.create_date, DateLoginModified = p.modify_date FROM sys.server_principals AS p JOIN sys.server_permissions AS perm ON perm.grantee_principal_id = p.principal_id WHERE p.type IN ('S','U','G') AND p.name <> N'sa' AND p.name NOT LIKE N'##%##';如果您看到任何權限，例如
CONTROL SERVER您想要刪除的權限，您可以使用類似REVOKE CONTROL SERVER FROM 'MyDomain\Login';. 請注意，該PermissionSql列將返回一個GRANTorDENY語句，您可以簡單地修改該 sql 文本以將其更改GRANT為REVOKE.