If you have CRM OnPremise, you can use a SQL query to get this information, but if you have CRM Online the only way to get this is to use API calls.
The following SQL queries should give most of what you want (though note that they were written before Access Teams were available, so may not be complete). You'll need appropriate SQL permissions to run these, as some of the data is not accessible through filtered views
-- Simple query on entity roles
select r.name as RoleName, r.businessunitidname as BusinessUnitName
, e.Name as EntityName, p.AccessRight, rp.PrivilegeDepthMask
from dbo.FilteredRole r
join dbo.RolePrivileges rp on r.roleid = rp.roleid
join dbo.FilteredPrivilege p on rp.privilegeid = p.privilegeid
join dbo.PrivilegeObjectTypeCodes potc on p.privilegeid = potc.privilegeid
join MetadataSchema.Entity e on potc.ObjectTypeCode = e.ObjectTypeCode
-- Query on entity roles, consolidating records
select RoleName, BusinessUnitName, EntityName
, max([Read]) as [Read], max([Write]) as [Write], max([Append]) as [Append], max([AppendTo]) as [AppendTo]
, max([Create]) as [Create], max([Delete]) as [Delete], max([Share]) as [Share], max([Assign]) as [Assign]
from -- Use sub query to split rights into columns, then outer query gets the Depth
(select r.name as RoleName, r.businessunitidname as BusinessUnitName, e.Name as EntityName
, isnull(case when p.AccessRight & 1 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Read] -- Use AccessRight to determine action
, isnull(case when p.AccessRight & 2 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Write]
, isnull(case when p.AccessRight & 4 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Append]
, isnull(case when p.AccessRight & 16 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [AppendTo]
, isnull(case when p.AccessRight & 32 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Create]
, isnull(case when p.AccessRight & 65536 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Delete]
, isnull(case when p.AccessRight & 262144 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Share]
, isnull(case when p.AccessRight & 524288 <> 0 then max(rp.PrivilegeDepthMask) end, 0) as [Assign]
from dbo.FilteredRole r
join dbo.RolePrivileges rp on r.roleid = rp.roleid
join dbo.FilteredPrivilege p on rp.privilegeid = p.privilegeid
join dbo.PrivilegeObjectTypeCodes potc on p.privilegeid = potc.privilegeid
join MetadataSchema.Entity e on potc.ObjectTypeCode = e.ObjectTypeCode
group by r.name, r.businessunitidname, e.Name, p.AccessRight) as Role
group by RoleName, BusinessUnitName, EntityName
-- General Privileges
select r.name as RoleName, r.businessunitidname as BusinessUnitName, p.Name as PrivilegeName
, max(rp.PrivilegeDepthMask) as Depth
from
dbo.FilteredPrivilege p
join dbo.RolePrivileges rp on rp.privilegeid = p.privilegeid
join dbo.FilteredRole r on r.roleid = rp.roleid
join dbo.PrivilegeObjectTypeCodes potc on p.privilegeid = potc.privilegeid
join MetadataSchema.Entity e on potc.ObjectTypeCode = e.ObjectTypeCode
-- Filter out Entity privileges
where p.Name <> 'prv'
+ case p.AccessRight
when 1 then 'Read'
when 2 then 'Write'
when 4 then 'Append'
when 16 then 'AppendTo'
when 32 then 'Create'
when 65536 then 'Delete'
when 262144 then 'Share'
when 524288 then 'Assign'
end -- Not all privileges reflect the entity name, so map those that differ
+ case
when e.IsActivity = 1 or e.Name = 'ActivityPointer' then 'Activity'
when e.Name = 'SalesOrder' then 'Order'
when e.Name = 'KbArticle' then 'Article'
when e.Name = 'Annotation' then 'Note'
when e.Name = 'SystemUser' then 'User'
when e.Name = 'Annotation' then 'Note'
when e.Name = 'BusinessUnitNewsArticle' then 'NewsArticle'
when e.Name = 'Template' then 'EmailTemplate'
when e.Name = 'KbArticleTemplate' then 'ArticleTemplate'
when e.Name = 'SalesProcessInstance' then 'AsyncOperation'
when e.Name = 'SavedQuery' then 'Query'
when e.Name = 'CustomerAddress' then 'Account'
when e.Name in ('SdkMessageRequestField', 'SdkMessageRequest', 'SdkMessageRequestInput', 'SdkMessagePair', 'SdkMessageFilter', 'SdkMessageResponse', 'SdkMessageResponseField') then 'SdkMessage'
when e.Name in ('ConstraintBasedGroup', 'Resource', 'ResourceSpec', 'ResourceGroup') then 'Service'
when e.Name in ('DiscountType', 'PriceLevel', 'UoMSchedule') then 'Product'
else e.Name
end
group by r.name, r.businessunitidname, p.Name
