Sql-Server

為什麼在 SSMS 的安全對象 GUI 上出現重複權限?

  • June 7, 2016

為什麼 Securables GUI 上出現重複權限?

在此處輸入圖像描述

有多個條目,因為您可以從不同的“授予者”多次授予相同的權限。如果該委託人(即“被授權人”)已經從兩個授權人那裡獲得了相同的權限,那麼您將在此螢幕上看到相同權限的三個條目。

對於您看到的“重複”條目,具有空“授予者”列的條目可用於分配來自不同授予者的相同權限,而不是其他條目中列出的相同權限(在這種情況下,它是: dbo)。當然,我不確定當您作為委託人連接時,為什麼會有額外的條目,因為每個授予人只能有一行,所以已經獲得了相同授予人的授予權限。例如,如果將“With Grant”選項更改為“dbo”,它將簡單地更新“Grantor”=“dbo”的條目。我猜當與連接相同的主體有一個非空的“Grantor”行時,GUI 不會檢查連接以過濾掉空行。再說一次,它的“Grantor”單元格是主體的下拉列表,允許選擇要在語句的AS子句中使用的主體GRANT,但事實並非如此,因此空行似乎比實際更令人困惑。

嘗試以下操作以查看此操作:

USE [tempdb];

CREATE USER [GrantPark] WITHOUT LOGIN;
CREATE USER [GrantWard] WITHOUT LOGIN;


CREATE TABLE dbo.MultiplePermissionsTest (Col1 INT);

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
      AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';


GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantPark] WITH GRANT OPTION;

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
      AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';


GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantWard];

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
      AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';


GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantWard] AS [GrantPark];

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
      AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

回報:

permission_name    state_desc                 Grantee      Grantor
(no rows)


permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo


permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo
SELECT             GRANT                      GrantWard    dbo


permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo
SELECT             GRANT                      GrantWard    dbo
SELECT             GRANT                      GrantWard    GrantPark

現在,轉到 SSMS 並展開“System Databases”文件夾,然後轉到“tempdb”,然後轉到“Tables”。右鍵點擊“dbo.MultiplePermissionsTest”並轉到“Properties”,然後轉到“Permissions”,然後選擇“GrantWard”。向下滾動一點,您應該會看到 3 行“Select”,每行都有不同的“Grantor”值:(空)dbo、 和GrantPark

引用自:https://dba.stackexchange.com/questions/140531