question

A quick Google search revealed several hits. This one borrowed from here: http://dba.stackexchange.com/questions/36618/list-all-permissions-for-a-given-role I think would be sufficient for what you are looking for. 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, [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] WHERE rp.type_desc = 'DATABASE_ROLE' AND pm.class_desc <> 'DATABASE' ORDER BY rp.name, rp.type_desc, pm.class_desc

I also consolidated below script for this but it runs per database. I need to execute this against all databases on the server. I tried wrapping it up in sp_msforeachdb but it fails because I'm not able to handle single quotes properly. SELECT [class_desc], p.[state_desc] AS [PermissionType] ,p.[permission_name] AS [PermissionName] ,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole] ,CASE p.[class] WHEN 0 THEN 'Database::' + DB_NAME() WHEN 1 THEN OBJECT_NAME(major_id) WHEN 3 THEN 'Schema::' + SCHEMA_NAME(p.[major_id]) END AS [ObjectName] FROM [sys].[database_permissions] p INNER JOIN sys.objects AS o ON p.major_id = o.object_id WHERE p.[class] IN (0, 1, 3) AND p.[minor_id] = 0 AND USER_NAME(p.[grantee_principal_id])='public' AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0