If you want to know where a give security role is used you have to check on:

1. Users having that specific security role
2. Teams having that specific security role
3. Any app configured to be shown only to users having that role
4. Any form configured to be shown only to users having that role

(If I'm missing something, please drop a comment!)

First 3 are quite easy to get. Simply look into systemuserroles, teamroles and appmoduleroles for a row having roleid equal to the role you want to check, and the game is set.

The forth is quite odd, actually. That info is not saved in any table, but is hidden in the formxml DisplayConditions node:

<DisplayConditions Order="0" FallbackForm="true">
    <Role Id="{97BBBE52-2191-EF11-8A6A-000D3AB91603}"/>
    <Role Id="{84EA22E3-2091-EF11-8A6A-000D3AB91603}"/>
    <Role Id="{42D555AA-2091-EF11-8A6A-000D3AB91603}"/>
    <Role Id="{583ACBD5-A482-EF11-AC20-000D3AB69188}"/>
    <Role Id="{CE3ECBD5-A482-EF11-AC20-000D3AB69188}"/>
</DisplayConditions>

This makes it quite complicated to retrieve with a simple query.

Luckly for us, there is an API that we can call to get that info easily: RetrieveDependentComponentsRequest.

var request = new RetrieveDependentComponentsRequest
{
    ComponentType = (int)ComponentType.Role, // 20
    ObjectId = componentId
};

It returns a list of entities describing all the metadata objects that are related to the one specified in the request. You need a bit of formatting on the response to get out something meaningful, but... it works, and it's also quite fast.

Lesson Learned

I lost half an hour trying to find where to get that info, and the SDK was already providing me the answer I needed. And I already knew it, because I created quite a while ago a PACX command to do the hard work for me:

Note for my future me: next time, before wasting time with useless data model scavenging, take a look again at the SDK! 😉