-----To get all DB role permission with object(sp/table/view) Details----------------------

 -----To get all DB role permission with object(sp/table/view) Details----------------------

SELECT DISTINCT rp.name, 

                ObjectType = rp.type_desc, 

                PermissionType = pm.class_desc, 

                pm.permission_name, 

                --pm.state_desc, 

                ObjectType = CASE 

                               WHEN obj.type_desc IS NULL 

                                     OR obj.type_desc = 'SYSTEM_TABLE' THEN 

                               pm.class_desc 

                               ELSE obj.type_desc 

                             END, 

                s.Name as SchemaName,

                [ObjectName] = Isnull(ss.name, Object_name(pm.major_id)),* 

FROM   sys.database_principals rp 

       INNER JOIN sys.database_permissions pm 

               ON pm.grantee_principal_id = rp.principal_id 

       LEFT JOIN sys.schemas ss 

              ON pm.major_id = ss.schema_id 

       LEFT JOIN sys.objects obj 

              ON pm.[major_id] = obj.[object_id] 

       LEFT JOIN sys.schemas s

              ON s.schema_id = obj.schema_id

WHERE  rp.type_desc = 'DATABASE_ROLE' 

       AND pm.class_desc <> 'DATABASE' 

--and obj.type_desc ='SQL_STORED_PROCEDURE'

--and rp.name='db_reader'

and Isnull(ss.name, Object_name(pm.major_id)) not like 'dt_%'

ORDER  BY rp.name, 

           pm.permission_name,

          pm.class_desc