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.

1 Answer 1


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 
            WHEN 'A' THEN 202 
                CASE prmssn.class 
                    WHEN 4 THEN 200 
                    ELSE 101 
        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.

  • 1
    Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
    – user115336
    Commented Jan 18, 2017 at 19:31
  • 1
    If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
    – Hannah Vernon
    Commented Jan 26, 2017 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.