Custom role report via SQL (business to IT to entitlement)

[#1]

We were unable to find a way to get a report that showed the Business Role to IT Role to Entitlement in one view. and in an easy to search/consume format.

I was able to create my own using SQL... no warranties here, so please verify your results!

To use this --- change the last line to correspond with your business role type/name (if you have multiple defined).

Below is configured for the out of box "business" type:

SELECT        spt_bundle.type AS [Role Type], spt_bundle.display_name AS [Business Role], spt_bundle.name AS [(Business Role Name)],
                         spt_bundle_1.display_name AS [IT Role], spt_bundle_1.name AS [(IT Role Name)], spt_profile_constraints.elt AS [Direct Entitlement]
FROM            spt_bundle INNER JOIN
                         spt_bundle_requirements ON spt_bundle.id = spt_bundle_requirements.bundle INNER JOIN
                         spt_bundle AS spt_bundle_1 ON spt_bundle_requirements.child = spt_bundle_1.id INNER JOIN
                         spt_profile ON spt_bundle_1.id = spt_profile.bundle_id INNER JOIN
                         spt_profile_constraints ON spt_profile.id = spt_profile_constraints.profile
WHERE        (spt_bundle.type = N'business')