Displaying Database and Server Principals

A quick example of using the system views to look at the server principals and the database principals. First, let's look at the database principals:

SELECT  dbp.class_desc                       AS GrantedObjectType,
        CASE dbp.class_desc
            WHEN 'DATABASE'         THEN DB_NAME    (dbp.major_id)
            WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(dbp.major_id)
            WHEN 'SCHEMA'           THEN SCHEMA_NAME(dbp.major_id)
        END                                  AS GrantedObjectName,
        COL_NAME(dbp.major_id, dbp.minor_id) AS GrantedColumnName,
        dbp.state_desc                       AS State,
        dbp.permission_name                  AS Permission,
        dpGrantor.type_desc                  AS GrantorType,
        dpGrantor.name                       AS Grantor,
        dpGrantee.type_desc                  AS GranteeType,
        dpGrantee.name                       AS Grantee,
        dpRole.name                          AS GranteeRole
   FROM sys.database_permissions                                     dbp
   JOIN sys.database_principals                                      dpGrantor
     ON dbp.grantor_principal_id = dpGrantor.principal_id
   JOIN sys.database_principals                                      dpGrantee
     ON dbp.grantee_principal_id = dpGrantee.principal_id
   JOIN sys.database_role_members                                    droGrantee
     ON dbp.grantee_principal_id = droGrantee.member_principal_id
   JOIN sys.database_principals                                      dpRole
     ON droGrantee.role_principal_id = dpRole.principal_id
   JOIN sys.objects                                                  o
     ON dbp.major_id = o.object_id
  ORDER BY GrantedObjectType,
        GrantedObjectName,
        Permission,
        State

The server principals are done in a similar way:

        
SELECT  svp.class_desc            AS GrantedObjectType,
        SCHEMA_NAME(svp.major_id) AS GrantedObjectName,
        svp.state_desc            AS State,
        svp.permission_name       AS Permission,
        spGrantor.type_desc       AS GrantorType,
        spGrantor.name            AS Grantor,
        spGrantee.type_desc       AS GranteeType,
        spGrantee.name            AS Grantee,
        spRole.name               AS GranteeRole
   FROM sys.server_permissions                                     svp
   JOIN sys.server_principals                                      spGrantor
     ON svp.grantor_principal_id = spGrantor.principal_id
   LEFT JOIN sys.server_principals                                 spGrantee
     ON svp.grantee_principal_id = spGrantee.principal_id
   LEFT JOIN sys.server_role_members                               sroGrantee
     ON svp.grantee_principal_id = sroGrantee.member_principal_id
   LEFT JOIN sys.server_principals                                 spRole
     ON sroGrantee.role_principal_id = spRole.principal_id
   LEFT JOIN sys.objects                                           o
     ON svp.major_id = o.object_id
  ORDER BY GrantedObjectType,
           GrantedObjectName,
           Permission,
           State