By using this website, you agree to our Terms of Use (click here)
Sometimes it's easier to setup User Security in a spreadsheet, rather than in Acumatica, then import the spreadsheet into Acumatica.
Here is an Excel file that you can use for this purpose:
There are two Worksheets in the Excel file:
Site Map - This is where you can manually populate the security. To populate this Worksheet, you can export the Site Map (SM200520) screen into a spreadsheet, then copy/paste the values into the Table in this Worksheet. Only keep the Screen ID, Title, Workspaces, and Category columns. Search for a comma in the Workspaces column to find any instances where a screen is listed in more than one Workspace and remove all Workspaces (including the commas) other than the first Workspace listed (if you don't do this, the import won't work). Then add additional columns for each User Role security group that you want to setup in Acumatica (make sure you setup each User Role in Acumatica before running the import). I setup User Role 1, User Role 2, and User Role 3 as examples in the attached spreadsheet.
Sheet1 - This Worksheet has the format needed by the Import Scenario and it gets generated from the Site Map Worksheet when you press Data -> Refresh All on the ribbon.
To prepare the Excel file, populate the Site Map Worksheet manually. Then edit the query that populates the Sheet1 Worksheet by doing the following:
1. Click Data -> Queries & Connections to make them available on the right-hand side:
2. On the right-hand side, right-click on Import and left-click on Edit:
3. Make sure that View -> Formula Bar is selected, then left-click Added Column on the right-hand side and change TIM - SALESDEMO to the name of your Tenant
4. The name of your Tenant that is needed can be found in the Access Rights by Role (SM201025) screen:
You can import the spreadsheet into Acumatica by doing the following:
1. Upload the spreadsheet to the Data Providers (SM206015) screen in Acumatica:
2. Setup an Import Scenario in the Import Scenarios (SM206025) screen in Acumatica:
3. Run the Import Scenario using the Import Scenarios (SM206025) screen.
I tried a simple test in 2020R1 and 2020R2 and it doesn't work seem to work: I get an error about "Error: The view doesn't exist."
Here is the import scenario. Am I missing something?
@ahmedbahar I am experiencing the same issue. I am on 2019R2, and managed to run a few traces. It seams to be some sort of issue with "View name: EntityRoles." in the import scenario. Have you had any luck getting this bug worked out?
@ahmedbahar If anyone else running across this error I have figured it out. I don't know why but you cannot import the .XML. You have to build it and follow the last screenshot of the import scenario to a tee. I ended up building the import scenario from scratch, being carful to add all the fields. There are 3 extra fields that that have to be added as well. Below is the step by step order I used to build it.
Name the Scenario.
Select Access Rights by Screen (SM20.10.20)
Select the Data Provider (also build from scratch)
*Target Object -> Field/Action Name*
Entities -> Path
EntityRoles -> Role (it will add 3 other fields automatically)
EntityRoles -> Access Rights
EntityRoles -> <Action: Save>
*you now have to add the 3 missing from the screenshot and move them to the correct order*
Entities -> <Parameter: NodeID>
Entities -> <Parameter: CacheName>
Entities -> <Parameter: MemberName>
This is the stage I had to add some extra columns to the screen to make sure they are filled in correctly. If you look at the screenshot I am missing data in a few of the Source Field/Value.
I don't know why those didn't carry over but that was a major issue on my end. I filled those in.
*Target Object -> Source Field/Value*
Path -> Path
Role -> Role
Access Rights -> Access Rights
The last step doesn't make much sense but following it allowed my import to work. You need to make sure the checkbox's are all checked EXACTLY as the screenshot. I had to uncheck "Commit" for Access Rights and uncheck "Active" for Role.
After following these steps I got it working. I have also updated Tim's Spreadsheet with some simple data validation to correct for reports having Granted & Revoked and all other screens have a larger set of options. Note if you leave them blank for any of the roles it should just stay as Not Set.
Hopefully this helps a few people.
Hello all,
I am on 2021R1 and somehow Adam's method can only update the first role
(I guess that's the result of unchecking "Active" for Role)
Per Tim and Adam's progress so far, I started from scratch once again.
and found that "Role" and "Role Description" are both key for view: EntityRoles
(I guess that's the reason for error: 1 items have not been processed successfully. View name: EntityRoles.)
So, I added "Role" and "Role Description" for input. then everything works as expected!
Hopefully this helps 🙂
@eva-yang Your post solved the mystery of why the entities wasn't working. I'm using this article in 2025 because there is nothing else out there to assist. I will say that between screens having one set of access and DB/GI's having another and then it also not liking titles with (space)/(space) in it we're hitting snags that are slowing us down in our user role overhaul project but we're getting there.
I just started working as administrator for the users and roles, and trying to wrap my head around between different roles and restrictions, plus adding new users, not knowing what kind of permission roles have (sooo many roles, and restrictions already set)
what is the best way, easiest way to have all the roles and restrictions exported so I can at lest understand what I am assigning to new users,
@nangel SM651500 Access Rights by Role or SM651700 Access Rights by Screen for all roles. I'm about to update this note with all the findings i'm getting to make this IS work with our access rights overhaul.
@timrodman - using the files from this article and following Eva's additional notes we just completed our user roles project. I put together a word doc detailing the steps I took to move our users from 53 different roles with each person having anywhere from 9 to 24 overlapping roles each to 1 user role for their dept, plus field level audit and internal user role for various standard items.
----------------------------------------------------------------------------------
Acumatica User File Processing Checklist
Prep Site Map Excel File:
~ Download your Site Map into Excel
Step 0 — You only need 4 columns: Workspaces, Category, Title and Screen ID
Goal: Remove unnecessary data
- Remove anything other that the 4 columns mentioned above from the Site Map
download.
Step 1 — Remove unnecessary blanks prior to download
Goal: Clean download without unnecessary blanks for cleaning up
- Download the site map without “is empty” in Workspaces.
- Manually remove all workspaces with alpha-numeric Workspace: These screens
don’t exist in Prod – we don’t use these modules.
Step 2 — Any titles on multiple workspaces need to have the multiple workspaces
removed according to these original instructions BUT…..
Goal: If you have a lot of separate departmental access roles, then you will need individual
lines on this spreadsheet for the titles in their individual workspaces.
- For all titles on multiple workspaces, create additional lines, one for each
workspace.
Step 3 — Edit any titles (in Site Map and/or GI name change) where there is a slash with or without spaces either side. For example: w/ w/o Account / Rep / Sales etc
Goal: Clean up titles because Acumatica treats slashes as a new leaf in the tree. The
import scenario will NOT work with a slash in the titles.
- Fix slashes in screen titles and GI titles
- Keep a clean Site Map without “slashes” in the titles, it’ll make your import scenario
work seamlessly.
Step 4 — Remove Rows by Prefix
Goal: Delete any row where the Screen ID (Column A) starts with certain prefixes since not
used by our tenant:
- Identify rows where Screen ID starts with: AM, CT, PJ, PH, FS, TC, FA, HP, HD, WZ, PM, PR,
SC. - Remove those rows from the spreadsheet
Step 5 — Remove Rows Ending with "PL"
Goal: Eliminate PL-related screens – access rights follow the screen to the PL, therefore
PL’s are not needed in this import scenario.
- Identify rows where Screen ID ends with PL.
- Remove those rows from the spreadsheet.
Step 6 — Copy Data and Edit using Formula
- Copy your downloaded site map data with columns: Workspaces, Category, Title and
Screen ID into columns A through D on the Site Map sheet of the ZImport File.
----------------------------------------------------------------------------------
Access Rights File:
Step 1 — On the ZImport Site Map sheet Add User Role 1, 2, & 3 titles – these will
match the names of your roles in Acumatica. Add or remove additional roles as
needed. I personally found it easiest to work on one role at a time.
~ The import scenario for this process is very time and system consuming and should
be done outside of business hours.
Step 2 — Update DB Access Levels
Goal: Standardize DB screen access to "Granted."
- For rows where Screen ID ends with DB
- If Access Level is: Delete, View Only, View, Granted
- Change it to Granted.
Step 2 — Update GI Access Levels
Goal: Standardize GI screens access to “Edit”
- For rows where Screen ID ends with GI
- If Access Level is: Delete, Edit, View Only, View, Granted
- Change it to Edit
Step 2.1 — Update REPORT Access Levels
Goal: Standardize GI screens access to "Revoked" or "Granted"
- For rows where Screen ID has a 60’s prefix after the letters:
- If Access Level is: Edit, View Only, View, Granted
- Change it to Granted
Step 3 — Make sure your title for the role matches – otherwise you’ll get Entityroles
error
Goal: Ensuring the Import Scenario works accurately.
- On the ZImport Site Map Sheet columns E through G add the role name to the Acumatica
role title EXACTLY.
Step 4 — Include all Revoked screens as these are children of the workspaces that will
be Granted, but then children revoked.
Goal: Ensuring the right level of access.
- On the Excel Import spreadsheet include all Revoked screens as these are children of the
workspaces that will be Granted, but then children revoked.
----------------------------------------------------------------------------------
Running the Import Scenarios
Step 1 — Create and run an import scenario just for the workspace (parent)
It needs it’s own Import Scenario (top level first because you can’t complete
breakdown levels until the top level has been implemented) to set parent level access
prior to completing the access rights Import Scenario for each role.
Data Providers: AccessRights2025Workspaces
Import Scenarios: AccessrightsByScreenImport2025Workspaces
Goal: Ensuring the right level of access to workspaces so that the full import can be
completed with success.
- On a new excel spreadsheet make sure the parent Workspace has it’s own role set to
Granted / Revoked
Step 6 — Run the Access Rights Import by Scenario
Data Providers: AccessRights2025
Import Scenarios: AccessrightsByScreenImport2025
Goal: Ensuring the right level of individual screen access
@astra-mathis thank you for the detailed instructions. I just copied the PDF as text into the discussion.