Practical SailPoint IdentityIQ SQL Queries for Entitlements, Roles, and Owners

IdentityIQ (IIQ) IIQ Community Knowledge Base
1

Problem

When investigating access in SailPoint IdentityIQ (IIQ), a few focused SQL queries can help answer common questions: Which roles contain an entitlement? What entitlements does a user have? Who owns an application or entitlement?

Diagnosis

This post collects read-only queries for those tasks, with examples you can adapt to your environment.

Solution

These examples use common IIQ table and column names. They have not been executed against your database; confirm the mappings for your IIQ version and add a database or schema prefix if required. SQL dialects, data types, and case sensitivity can differ between installations.

Replace the sample identity names, application names, and entitlement values with your own. An entitlement’s raw value can differ from its display name. For Active Directory, for example, the value may be a full group distinguished name (DN).

Use an approved read-only database connection. When putting these queries into an application or script, bind parameters instead of concatenating user input into SQL. For a large input list, consider a staging table or your database’s supported list-parameter mechanism rather than an oversized IN clause.

1. Find bundles containing any entitlement in a list

IIQ stores roles as bundles. Where available, spt_bundle_profile_relation provides an indexed relationship between bundles and entitlement profiles.

The following query returns bundles associated with any entitlement value in the list. It does not require a bundle to contain every listed entitlement.

SELECT DISTINCT
    b.id                  AS bundle_id,
    b.name                AS bundle_name,
    b.type                AS bundle_type,
    a.name                AS application_name,
    r.attribute           AS entitlement_attribute,
    r.value               AS entitlement_value,
    source_b.name         AS source_bundle_name
FROM spt_bundle_profile_relation r
JOIN spt_bundle b
    ON b.id = r.bundle_id
LEFT JOIN spt_bundle source_b
    ON source_b.id = r.source_bundle_id
LEFT JOIN spt_application a
    ON a.id = r.source_application
WHERE r.value IN (
    'ENTITLEMENT_VALUE_1',
    'ENTITLEMENT_VALUE_2',
    'ENTITLEMENT_VALUE_3'
)
ORDER BY
    b.name,
    a.name,
    r.value;

The result includes the matching entitlement and its originating role, so a bundle can appear on multiple rows. Indexed associations can include access originating through another role; inspect source_bundle_name when interpreting the results.

To restrict the query to Active Directory group memberships, add these conditions before ORDER BY, using your exact application and schema attribute names:

AND a.name = 'ActiveDirectory'
AND r.attribute = 'memberOf'

Application and attribute filters matter because the same entitlement value can occur in different applications or attributes.

Investigating missing role associations

The association table is an index. Missing or stale entries can affect results, so it should not be treated as an unconditional inventory of every role definition.

For a known bundle, inspect its underlying profile constraints:

SELECT
    b.name  AS bundle_name,
    p.id    AS profile_id,
    pc.elt  AS profile_constraint
FROM spt_bundle b
JOIN spt_profile p
    ON p.bundle_id = b.id
JOIN spt_profile_constraints pc
    ON pc.profile = p.id
WHERE b.name = 'BUNDLE_NAME';

This diagnostic query returns stored constraint content, not a normalized entitlement list. Constraints can contain operators and compound conditions, so a text match alone does not establish that a role grants a particular entitlement. Check the role definition and association-index state through IIQ if the results disagree.

The table mappings and indexing considerations are illustrated in the SailPoint community discussions on role composition and bundle-profile relationships.

2. Fetch a user’s recorded entitlements

Use spt_identity_entitlement to retrieve entitlement records associated with an IIQ identity. The query includes the account identifier so you can distinguish multiple accounts on the same application.

SELECT
    i.name              AS identity_name,
    a.name              AS application_name,
    ie.native_identity  AS account_name,
    ie.name             AS entitlement_attribute,
    ie.value            AS entitlement_value
FROM spt_identity_entitlement ie
JOIN spt_identity i
    ON i.id = ie.identity_id
LEFT JOIN spt_application a
    ON a.id = ie.application
WHERE i.name = 'USER_NAME'
  AND ie.name NOT IN ('assignedRoles', 'detectedRoles')
ORDER BY
    a.name,
    ie.native_identity,
    ie.name,
    ie.value;

Replace USER_NAME with the IIQ Identity name. The query excludes assigned-role and detected-role entries; it does not restrict results to directly requested entitlements or prove how each entitlement was granted.

For only Active Directory results, add this before ORDER BY:

AND a.name = 'ActiveDirectory'

Results reflect IIQ’s stored entitlement records, not a live query of the target systems. Their completeness and currency depend on aggregation, refresh processing, and the entitlement-record lifecycle in your deployment. Investigate unexpected duplicate or stale records before treating this as a definitive access inventory. SailPoint community examples show the identity-entitlement fields and role-entry distinction.

3. Fetch owners for a list of applications

Join the application owner reference to spt_identity to retrieve the owner’s name and email.

SELECT
    a.name         AS application_name,
    i.name         AS owner_name,
    i.display_name AS owner_display_name,
    i.email        AS owner_email
FROM spt_application a
LEFT JOIN spt_identity i
    ON i.id = a.owner
WHERE a.name IN (
    'ActiveDirectory',
    'Application2',
    'Application3'
)
ORDER BY a.name;

Replace the sample names with the exact application names in IIQ. The LEFT JOIN retains matching applications when their owner reference is empty or cannot be resolved; the owner columns then contain NULL.

If the owner is a workgroup, this query returns the workgroup identity. It does not expand the workgroup’s members. An application name that does not exist in the database produces no row.

4. Fetch owners for a list of entitlements

Entitlements in the IIQ Entitlement Catalog are represented as managed attributes. Query spt_managed_attribute and join its owner reference to spt_identity.

SELECT
    a.name          AS application_name,
    ma.attribute    AS entitlement_attribute,
    ma.value        AS entitlement_value,
    i.name          AS owner_name,
    i.display_name  AS owner_display_name,
    i.email         AS owner_email
FROM spt_managed_attribute ma
LEFT JOIN spt_application a
    ON a.id = ma.application
LEFT JOIN spt_identity i
    ON i.id = ma.owner
WHERE ma.value IN (
    'ENTITLEMENT_VALUE_1',
    'ENTITLEMENT_VALUE_2',
    'ENTITLEMENT_VALUE_3'
)
ORDER BY
    a.name,
    ma.value;

Use the raw entitlement values, not display names. To limit the matches to Active Directory group entitlements, add these conditions before ORDER BY:

AND a.name = 'ActiveDirectory'
AND ma.attribute = 'memberOf'

This query returns the configured entitlement owner. It does not substitute the application owner when the entitlement owner is missing. IIQ can use fallback approval behavior, but that is separate from the stored ownership field. See SailPoint’s Entitlement Catalog documentation.

An entitlement with no resolvable owner still appears, with NULL owner fields. An input value absent from the catalog produces no row. As with application owners, a workgroup owner is returned as an identity rather than expanded into individual members.

Choosing the right query

Question Starting table Key input
Which bundles reference these entitlements? spt_bundle_profile_relation Entitlement values, preferably scoped by application and attribute
What constraints are defined on a particular bundle? spt_profile and spt_profile_constraints Bundle name
What entitlements are recorded for this user? spt_identity_entitlement IIQ Identity name
Who owns these applications? spt_application Application names
Who owns these entitlements? spt_managed_attribute Entitlement values, preferably scoped by application and attribute

Start with one known identity, application, or entitlement and compare the output with IIQ before expanding to a larger list. That gives you a concrete check of both the schema mappings and the meaning of the returned data.

2 Likes