Why are duplicate permissions appearing on the securables GUI in SSMS?

Question

Why are duplicate permissions appearing on the Securables GUI?

Answer 1

There are multiple entries because you can be granted the same permission multiple times, from different "grantors". If that principal (i.e. the "grantee") were already granted that same permission from two grantors, then you would see three entries for that same permission on this screen.

For the "duplicate" entries that you are seeing, the one with the empty "Grantor" column could be used to assign that same permission from a different grantor than the one listed in the other entry for that same permission (in this case it is: dbo). Of course, I am not sure why there is that extra entry when you are connected as principal that already has a granted permission for what would be the same grantor since you can only have one row per grantor. If you change the "With Grant" option as "dbo", for example, it will simply update the entry for "Grantor" = "dbo". I guess the GUI does not check the connection to filter out the empty rows when there is a non-empty "Grantor" row for the same principal as the connection. Then again, it would make sense to have that empty row if it the "Grantor" cell was a drop-down of principals that would allow for selecting one to be used in the AS clause of the GRANT statement, but it isn't, so having the empty row seems more confusing than practical.

Try the following to see this in action:

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';

Returns:

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

Now, go to SSMS and expand the "System Databases" folder, then go to "tempdb", and then to "Tables". Right-click on "dbo.MultiplePermissionsTest" and go to "Properties", then to "Permissions", and select "GrantWard". Scroll down a little and you should see 3 rows for "Select", each with a different value for "Grantor": (empty), dbo, and GrantPark.