SQL Query to retrieve all the entitlements - DisplayName for a user

[void0703]

Hi All,

I want a SQL query that will give me the list of user along with the entitlements they have assigned to them.

Please let me know if we can do it.

Thanks,

[pmahalle · Accepted solution]

Hi @void0703 ,

Use below query, which will give you list of all the entitlements assigned to user across all the endpoints

SELECT
U.USERNAME, E.ENTITLEMENT_VALUE
FROM
USERS U, USER_ACCOUNTS UA, ACCOUNTS A, ACCOUNT_ENTITLEMENTS1 AE, ENTITLEMENT_VALUES E
WHERE
U.USERKEY = UA.USERKEY AND
UA.ACCOUNTKEY = A.ACCOUNTKEY AND
A.ACCOUNTKEY = AE.ACCOUNTKEY AND
AE.ENTITLEMENT_VALUEKEY = E.ENTITLEMENT_VALUEKEY

[DixshantValecha]

Hi @void0703 ,

Please validate the below query:-

SELECT
U.USERNAME,
E.ENTITLEMENT_VALUE
FROM
USERS U
JOIN
USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY
JOIN
ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN
ACCOUNT_ENTITLEMENTS1 AE ON A.ACCOUNTKEY = AE.ACCOUNTKEY
JOIN
ENTITLEMENT_VALUES E ON AE.ENTITLEMENT_VALUEKEY = E.ENTITLEMENT_VALUEKEY;