Print this page

Remove permission set assignments for a subset of users, starting with the user’s name, using excel

Knowledge Article Number 000233952
Description There are users in my org who have permission sets that give them permission to do things I don't want them to. I was given a list of names, and now I need to remove the permission set from these users.

 
Resolution NOTE: This works for any subset of records you need to query to get Ids, not just users
  1. Get user Id’s from names
    1. Organize the names so that they can be queried using Data Loader
      1. Open the CSV that contains the names of the users (assuming that the names are all in one column)
      2. In the cell next to the first name, enter the formula = “’” & X & “’ ,”
        1. Where X is the value of the first cell (ie A1)
  1. Drag this formula down the column until you get to the last name
  2. Copy this new column
  3. In another column, paste, using the special ‘paste values’ so that the new column only has the values, rather than the formula
  4. In yet another column, next to the first entry, enter the formula = X
    1. Where X is the first cell in the last column we just made
  5. In the cell below this, enter the formula = X & Y
    1. Where X is the cell to the left of this cell, and Y is the cell above this cell
  6. Drag this new formula all the way down to the last entry
  7. The very last cell will now contain a concatenated string that we can use to query for all these user’s Ids; please note that you will need to manually remove the trailing comma
  1. Perform the query
    1. You will now query either using Data Loader or Workbench, and select User as the object
      1. This should look like: Select Id, Username, Name FROM User WHERE Name IN (X)
        1. Where X is the string of user names we just created
  1. ​​Get PermissionSetAssignment Ids from user Id and Permission set Id
    1. Organize Ids so that they can be queried using Data Loader
      1. You will use the same process as we used in 1.a to get a string we can query with
      2. You will need to also get the Id of the permission set you want removed
    2. Perform the query
      1. You will now query either using Data Loader or Workbench
        1. This should look like: Select Id FROM PermissionSetAssignment WHERE AssigneeId IN (X) AND PermissionSetId = Y
          1. Where X is the string we just created out of the Ids and Y is the permission set id
  2. Delete the permission set assignment using Data Loader
    1. Use the resulting CSV to perform a delete on the PermissionSetAssignment
Warnings: 
You cannot have any apostrophes in a name if you are performing a query (IE o'connell), without adding an escape character before the apostrophe. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm

A query cannot be longer than 20,000 characters; if you are performing a long query it may need to be broken up into several queries, depending on how long it is.




promote demote