0

I'm looking to find all names of profiles in a certain permission set. Preferably, I would like to do this in one SOQL query.

For example, I have permission set 1 and in 1, there are 5 users with profiles:

A, A, B, C, D

respectively.

I want to see a SOQL query that will summarize that in permission set 1, there are profiles A, B, C, and D.

This is what I have so far:

SELECT Assignee.Id, Assignee.Name, Assignee.Profile.Name, PermissionSet.Id, PermissionSet.Name 
FROM Profile 
WHERE PermissionSetId IN 
(SELECT Id FROM PermissionSet WHERE Id=PERMSET1 OR Id = PERMSET2 OR Id = PERMSET3 OR Id = PERMSET4)

When I run this in Developer Console, I see that Assignee.Profile returns an [object Object] and I can't see the column with Assignee.Profile.Name. Also, I'm stuck at this step and I don't know how to group all profile names together.

4
  • "user profiles in a certain permission set" - what does it mean? or you want list of users are assigned to which profiles and permission sets? Commented Aug 15, 2017 at 21:32
  • I edited my question to clarify what I'm hoping to achieve.
    – akcorp2003
    Commented Aug 15, 2017 at 21:47
  • permission sets are not related to profiles, they are assigned to an user Commented Aug 15, 2017 at 21:53
  • Yes. But there are users in permission sets and I'm looking for the profile names that those users have.
    – akcorp2003
    Commented Aug 15, 2017 at 22:05

3 Answers 3

1

To get around the [object Object] limitation of the Developer Console, use GROUP BY. The following query should get you started.

SELECT PermissionSet.Name PermissionSetName, Assignee.Profile.Name UserProfile, COUNT(AssigneeId)
FROM PermissionSetAssignment
WHERE ...
GROUP BY CUBE (PermissionSet.Name, Assignee.Profile.Name)
ORDER BY PermissionSet.Name DESC NULLS LAST, Assignee.Profile.Name DESC NULLS LAST

This particular query is designed to show you the same output you'd see in a matrix report, showing each permission set by name, followed by profile, followed by a summary row for each permission set name, eventually followed by the totals per profile, and a grand total at the bottom.

Or, consider using the Data Loader, dataloader.io, workbench, or basically any "real" query application. The Developer Console's Query Execution Planner is particularly useful, but the query output windows are not incredibly useful except in trivial cases, since they can't show child relationships and can't show grandparent or farther parent relationships.

7
  • When I try running this query, I get this error: duplicate alias: Name
    – akcorp2003
    Commented Aug 15, 2017 at 22:21
  • @akcorp2003 You need the "aliases" (PermissionSetName and UserProfile) or you'll get that error. You might want to start with this query verbatim (minus the where line) to see what it looks like.
    – sfdcfox
    Commented Aug 15, 2017 at 22:22
  • I think I'm getting somewhere with this. I tried copying this query (minus the COUNT() and WHERE) into Data Loader but it said that the error is "No such field name permissionsetname on entity PermissionSet."
    – akcorp2003
    Commented Aug 15, 2017 at 22:49
  • Also, why are there NULLS in the permission set names?
    – akcorp2003
    Commented Aug 15, 2017 at 22:50
  • @akcorp2003 The nulls are summary rows, like you'd see in a summary report. (Assignee.Profile.Name) null is a summary row for the permission set name, (PermissionSet.Name) is the summary row for the profile name, and both nulls are the grand total rows. I built this query specifically to be used in the Developer Console. With the data loader, the aliases and groupings are not necessary.
    – sfdcfox
    Commented Aug 15, 2017 at 22:52
1

Here is some code that will give you the Profile Names in an grouped list.

List<PermissionSetAssignment> assignedUsers = [SELECT AssigneeId FROM PermissionSetAssignment WHERE PermissionSetId =: '0PSj0000000A1PSGA0'];
List<Id> userIds = new List<Id>();
for(PermissionSetAssignment a : assignedUsers)
{
    userIds.add(a.AssigneeId);
}

AggregateResult[] groupedResults = [SELECT Profile.Name
                                    FROM User
                                    WHERE Id IN: userIds
                                    GROUP BY Profile.Name];

system.debug(groupedResults);

List<String> ProfileNames = new List<String>();

for(AggregateResult ar : groupedResults)
{
    ProfileNames.add((string)ar.get('Name'));
}

system.debug(ProfileNames);

Debug results for me:

(AggregateResult:{Name=AWD Sales}, AggregateResult:{Name=Integration User}, AggregateResult:{Name=Marketing}, AggregateResult:{Name=Security/Exec Mgmnt}, AggregateResult:{Name=System Administrator})

and

(AWD Sales, Integration User, Marketing, Security/Exec Mgmnt, System Administrator)
1
  • This definitely a potential solution! However, I'm looking for one SOQL query to do it all.
    – akcorp2003
    Commented Aug 15, 2017 at 22:50
0

Permission sets are not related to a profile, then are related to an user via PermissionSetAssignment.

Not sure if this SOQL will help you:

SELECT PermissionSet.Name, Assignee.Id, Assignee.Name, Assignee.ProfileId
FROM PermissionSetAssignment
WHERE permissionsetid IN ('PERMSET1', 'PERMSET2', ..)
ORDER BY PermissionSet.Name
1
  • Unfortunately, this is grouping by the permission set assignment names. While I can see the profile ids, I want to be able to see a summary of the profile names.
    – akcorp2003
    Commented Aug 15, 2017 at 22:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .