Sql-server – SQL Server displayed ‘Grantor’ vs system tables result

permissionssql serversql-server-2008-r2

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

The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.

Best Answer

SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.

You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.

I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:

DECLARE  @_msparam_0 nvarchar(4000);

SET @_msparam_0 = '##MS_AgentSigningCertificate##';

SELECT ascii(prmssn.state) AS [PermissionState]
    , null AS [Code]
    , grantor_principal.name AS [Grantor]
    , prmssn.type AS [SqlCodePP]
    , CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN 
                CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id) 
                    WHEN 'R' THEN CASE prmssn.class 
                    WHEN 4 THEN 201 
                    ELSE 301 
                END 
            WHEN 'A' THEN 202 
            ELSE 
                CASE prmssn.class 
                    WHEN 4 THEN 200 
                    ELSE 101 
                END 
            END 
        ELSE prmssn.class 
        END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
    INNER JOIN sys.server_principals AS grantor_principal ON
        grantor_principal.principal_id = prmssn.grantor_principal_id
    INNER JOIN sys.server_principals AS grantee_principal ON 
        grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
    AND (grantee_principal.name = @_msparam_0);

This contains the same joins as in your query. I've reformatted the query for readability.

On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.

Related Question