Why are duplicate permissions appearing on the Securables GUI?
1 Answer
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
.