Vryjdfkk

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.

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.