1

I have a role named db_executor and need to find out what permissions it has on what objects. I found List all permissions for a given role? but the only solution that remotely works is:

SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.[name] = 'db_executor'

The problem is that the ObjectName and ObjectType is NULL. So while I know it only has the EXECUTE permission in a state of GRANT I don't know to what object(s) that applies to. Is there a better way to get this list or how can I modify this code to list the Object(s)?

1

The script is missing a left join to sys.types to grab table type information, which also requires execute permissions to use (alternatively join sys.table_types, you'll get the same data). Try the following:

SELECT  DB_NAME() AS 'DBName' ,
    p.[name] AS 'PrincipalName' ,
    p.[type_desc] AS 'PrincipalType' ,
    p2.[name] AS 'GrantedBy' ,
    dbp.[permission_name] ,
    dbp.[state_desc] ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[name], t.name)
    END AS 'ObjectName' ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[type_desc], N'TYPE')
    END AS 'ObjectType' 
FROM    [sys].[database_permissions] dbp
        LEFT JOIN [sys].[all_objects] so ON dbp.[major_id] = so.[object_id]
        LEFT JOIN [sys].[database_principals] p ON dbp.[grantee_principal_id] = p.[principal_id]
        LEFT JOIN [sys].[database_principals] p2 ON dbp.[grantor_principal_id] = p2.[principal_id]
        LEFT JOIN [sys].[types] t ON dbp.major_id = t.user_type_id
WHERE   p.[name] = 'db_executor';
5
  • That returns a Objectype (Table?_TYPE) but still null for the ObjectName Nov 11 '16 at 22:12
  • How many rows are you getting out of this? And what is the class_desc from database_permissions for the null objects?
    – Nic
    Nov 11 '16 at 22:29
  • I am getting a single row returned and the class_desc is "DATABASE". Also, sorry for the delay, I was not working over the weekend. Nov 14 '16 at 14:17
  • I've updated the script to include the database scoped permission.
    – Nic
    Nov 14 '16 at 14:24
  • Great, that works perfectly. Thank you for your help. Nov 14 '16 at 14:27
1

Below code gives all the roles

SELECT  DB_NAME() AS 'DBName' ,
        p.[name] AS 'PrincipalName' ,
        p.[type_desc] AS 'PrincipalType' ,
        p2.[name] AS 'GrantedBy' ,
        dbp.[permission_name] ,
        dbp.[state_desc] ,
        so.[name] AS 'ObjectName' ,
        so.[type_desc] AS 'ObjectType'
        --, so.*
FROM    [sys].[database_permissions] dbp
        LEFT JOIN [sys].[objects] so ON dbp.[major_id] = so.[object_id]
        LEFT JOIN [sys].[database_principals] p ON dbp.[grantee_principal_id] = p.[principal_id]
        LEFT JOIN [sys].[database_principals] p2 ON dbp.[grantor_principal_id] = p2.[principal_id]

I only found ObjectName, ObjectType only for public role

enter image description here

And following values

enter image description here

4
  • This returns 47759 rows, way to many to dig though. I only need it for my single row as defined in the OP. Nov 11 '16 at 22:16
  • HI Matthew, i dont have db_executor in my database, but another script below by Nic gives 1855 Nov 11 '16 at 22:17
  • Which column should I be doing a where clause in your code to narrow it down? Your count would be different then mine since we have different DBs but my results is almost 50k, to many to dig though looking for a single role Nov 11 '16 at 22:18
  • I tried on p.type_dec but it did not return anything, so either I tried the wrong column or this is not pulling all roles. Nov 11 '16 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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