Ed-Fi Claimset Diagnostic

DECLARE @ClaimSetName Varchar(60) = 'SIS Vendor'; 

select * from 
(
-- Get All of the resources tied directly to the claimset and resolve thier permissions and authorization strategies from the hierarchy, respecting any overrides if present.
SELECT a.ActionName
      ,rc.ResourceName
	  ,ast.AuthorizationStrategyName
  FROM [dbo].[ClaimSetResourceClaims] cr
  inner join dbo.ClaimSets cs on cs.ClaimSsetId = ClaimSet_ClaimSetId
  inner join dbo.ResourceClaims rc on rc.ResourceClaimId = cr.ResourceClaim_ResourceClaimId
  inner join dbo.Actions a on a.ActionId = Action_ActionId
  left join dbo.ResourceClaimAuthorizationMetadatas m on m.Action_ActionId = a.ActionId and m.ResourceClaim_ResourceClaimId = Coalesce(rc.ParentResourceClaimId, rc.ResourceClaimId)
  inner join dbo.AuthorizationStrategies ast on ast.AuthorizationStrategyId = Coalesce (cr.AuthorizationStrategyOverride_AuthorizationStrategyId, m.AuthorizationStrategy_AuthorizationStrategyId)
  where cs.ClaimSetName = @ClaimSetName

Union

-- Get All of the resources tied to a resource parent (group) that is tied to the claimset and resolve thier permissions and authorization strategies from the hierarchy, respecting any overrides if present.
SELECT a.ActionName
      ,rc.ResourceName
	  ,ast.AuthorizationStrategyName
  FROM [EdFi_Security].[dbo].[ClaimSetResourceClaims] cr
  inner join dbo.ClaimSets cs on cs.ClaimSetId = ClaimSet_ClaimSetId
  inner join dbo.ResourceClaims rc on rc.ParentResourceClaimId = ResourceClaim_ResourceClaimId
  inner join dbo.Actions a on a.ActionId = Action_ActionId
  left join dbo.ResourceClaimAuthorizationMetadatas m on m.Action_ActionId = a.ActionId and m.ResourceClaim_ResourceClaimId = Coalesce(rc.ParentResourceClaimId, rc.ResourceClaimId)
  inner join dbo.AuthorizationStrategies ast on ast.AuthorizationStrategyId = Coalesce (cr.AuthorizationStrategyOverride_AuthorizationStrategyId, m.AuthorizationStrategy_AuthorizationStrategyId)
  where cs.ClaimSetName = @ClaimSetName) as wrapper
--optional filter
--where ResourceName in ('assessmentReportingMethodType')
Order by ResourceName, ActionName