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

**URL:** <https://developer.sailpoint.com/discuss/t/practical-sailpoint-identityiq-sql-queries-for-entitlements-roles-and-owners/225697>\
**Category:** IIQ Community Knowledge Base\
**Tags:** identityiq\
**Created:** [October 16, 2026, 2:16am UTC](https://developer.sailpoint.com/discuss/t/practical-sailpoint-identityiq-sql-queries-for-entitlements-roles-and-owners/225697 "2026-10-16T02:16:47Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![kumarSinghM](https://avatars.discourse-cdn.com/v4/letter/k/ecb155/32.png) [@kumarSinghM](https://developer.sailpoint.com/discuss/u/kumarSinghM)\
**Post date:** [October 16, 2026, 2:16am UTC](https://developer.sailpoint.com/discuss/t/practical-sailpoint-identityiq-sql-queries-for-entitlements-roles-and-owners/225697/1 "2026-10-16T02:16:48Z")

</div>

## 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.

```sql
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:

```sql
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:

```sql
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](https://developer.sailpoint.com/discuss/t/identityiq-role-composition-including-inheritance/137493) and [bundle-profile relationships](https://developer.sailpoint.com/discuss/t/bundle-profile-relationship-table-entitlement-role-relationship/217128).

## 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.

```sql
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`:

```sql
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](https://developer.sailpoint.com/discuss/t/certification-launch-fails-on-deepcopy-error/220901/14).

## 3. Fetch owners for a list of applications

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

```sql
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`.

```sql
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`:

```sql
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](https://documentation.sailpoint.com/identityiq/help/application_management/entitlement_catalog.html).

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.