Symptoms
New Role Members or role owners or leaders report they do not have all the access they need, after role is provided/provisioned.
Diagnosis
Roles do not fully provide new members all the access they need.
Solution
Review all members and their shared exception entitlements, for all members.
This can be accomplished by running the below query in a NON-PROD IIQ Database environment, that is typically a daily copy of PROD database. Once you have ran the below, you can send the results to the role owners to see if additional access should be added to their Role(s). You can add a filter on bundle.type if needed
SELECT * FROM (
SELECT
[YourDatabaseName].spt_bundle.name as [AssignedAP]
,[YourDatabaseName].spt_role_scorecard.members AS [APMemberCount]
,[YourDatabaseName].spt_role_scorecard.provisioned_ent
,[YourDatabaseName].spt_identity_entitlement.value
,[YourDatabaseName].spt_identity_entitlement.name as [type]
,count ([YourDatabaseName].spt_identity_entitlement.value) as [MemberExceptionCount]
,[YourDatabaseName].spt_application.name as [application]
,[Owner].name as [RoleOwner]
,[Owner].email as [RoleOwnerEmail]
FROM [YourDatabaseName].spt_identity_entitlement
Left Join [YourDatabaseName].spt_identity
ON [YourDatabaseName].spt_identity_entitlement.identity_id = [YourDatabaseName].spt_identity.id
left join [YourDatabaseName].spt_identity_assigned_roles on [YourDatabaseName].spt_identity.id = [YourDatabaseName].spt_identity_assigned_roles.identity_id
left join [YourDatabaseName].spt_bundle on [YourDatabaseName].spt_identity_assigned_roles.bundle = [YourDatabaseName].spt_bundle.id and [YourDatabaseName].spt_bundle.type = 'enterprise'
LEFT JOIN [YourDatabaseName].spt_role_scorecard ON [iam_prod].[spt_bundle].ID = [YourDatabaseName].spt_role_scorecard.role_id
left join [YourDatabaseName].spt_application on [YourDatabaseName].spt_identity_entitlement.application = [YourDatabaseName].spt_application.id
left join [YourDatabaseName].spt_identity AS [Owner] on [YourDatabaseName].spt_bundle.owner = [Owner].id
WHERE [YourDatabaseName].spt_identity_entitlement.granted_by_role = '0'
and [YourDatabaseName].spt_application.name is not null
and [YourDatabaseName].spt_bundle.name is not null
Group by [YourDatabaseName].spt_bundle.name
,[YourDatabaseName].spt_role_scorecard.members
,[YourDatabaseName].spt_role_scorecard.provisioned_ent
,[YourDatabaseName].spt_identity_entitlement.value
,[YourDatabaseName].spt_identity_entitlement.NAME
,[YourDatabaseName].spt_application.name
,[Owner].name as [RoleOwner]
,[Owner].email as [RoleOwnerEmail]
) AS temp WHERE [MemberExceptionCount] = [APMemberCount]
