Get list of permisison for a SQL role

Question

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)?

Nic · Accepted Answer · 2016-11-14T14:24:19.210

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';

edited Nov 14 '16 at 14:24

answered Nov 11 '16 at 22:11

Nic

4,013
1
15
21

That returns a Objectype (Table?_TYPE) but still null for the ObjectName – Matthew Verstraete 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. – Matthew Verstraete 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. – Matthew Verstraete Nov 14 '16 at 14:27

score 1 · Answer 2 · answered Nov 11 '16 at 22:13

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

And following values

answered Nov 11 '16 at 22:13

Raghu Ariga

278
1
10

This returns 47759 rows, way to many to dig though. I only need it for my single row as defined in the OP. – Matthew Verstraete Nov 11 '16 at 22:16
HI Matthew, i dont have db_executor in my database, but another script below by Nic gives 1855 – Raghu Ariga 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 – Matthew Verstraete 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. – Matthew Verstraete Nov 11 '16 at 22:24

Get list of permisison for a SQL role

2 Answers2