January 18, 2017 at 6:24 am
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?
My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_id
January 18, 2017 at 3:13 pm
joseph.uher - Wednesday, January 18, 2017 6:24 AMWhen I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_id
When you are viewing the permissions through SSMS, you can change permission in that window so it displays all possible permissions for each login. You need make sure to look at just the permissions that have been granted (or have a check in the checkbox). I am seeing the same grantor when looking at just those permissions that have been granted.
Sue
January 19, 2017 at 6:13 am
Sue_H - Wednesday, January 18, 2017 3:13 PMjoseph.uher - Wednesday, January 18, 2017 6:24 AMWhen I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_idWhen you are viewing the permissions through SSMS, you can change permission in that window so it displays all possible permissions for each login. You need make sure to look at just the permissions that have been granted (or have a check in the checkbox). I am seeing the same grantor when looking at just those permissions that have been granted.
Sue
Sue: Thanks so much for calling my attention to granted permissions. That does explain the display difference between SSMS and system tables for me. Both grantors -- sa and our login account (sa-level) show a 'CONNECT SQL' permission, but only the sa permission has been granted (checked). Issue resolved.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply