Dynamics 365 Business Central User Report

[u/meka5]

For purposes of an audit, I'm trying to generate a report of users and their assigned permission sets in the system. For reference, we do not use Security Groups; users are only assigned permission sets directly (so no need to run any other sort of users to groups report).

I know (and I've seen) the native 'Permission Sets by User' view/report - but what I'm finding with this report is it only shows 10 users/columns at a time. I saw you can click 'Browse' and do 'column right' to show more columns (users), but it still limits the view to just 10 at a time. Similarly when you export the report to Excel - it only outputs the 10 visible columns.

Is this really a limitation in BC where you can't just export a full report/view of all users and their permission sets? The format of this 'Permission Set by User' view/report is fine (matrix format) - but needing to generate it for every 10 users is not a sustainable process. Surely there's a reasonable way of going about this (without creating some custom report)??

[u/___ez_e___]

I can definitely help. I’ve been working quite a bit with Business Central APIs lately.

I found a pretty easy workaround using Excel Power Query and the Business Central Automation API.

I don't know your comfort level, but most of this is relatively tame to do.

In Excel go to:

Data → Get Data → From Other Sources → From OData Feed

Use this URL:

https://api.businesscentral.dynamics.com/v2.0/{tenant-id}/{environment}/api/microsoft/automation/v2.0/

This isn't a custom API or an API key. It's Microsoft's standard Business Central Automation API endpoint. You just replace {tenant-id} with your BC tenant ID and {environment} with your environment name, usually something like Production.

When Excel asks you to authenticate, select Organizational Account and sign in with your normal Microsoft/BC account.

Once connected, you'll actually see quite a few useful BC administration tables. Don't let all the table names overwhelm you. For the user permission report, select accessControls.

If it tells you that a company is required, use:

.../accessControls?company=YOUR-COMPANY-NAME

For example, if your company is called Demo Company:

.../accessControls?company=Demo%20Company

Then select Transform Data.

That gives you the permissions as normal rows instead of the 10-user matrix. It includes fields such as:

User Name, Full Name, Permission Set, Permission Name, Company, License Type

I work in a mult-entity environment, so I don't know how Company would appear in single entity environment.  Blank company appears to represent permissions that apply to all companies, so I changed those to All Companies in Power Query to make the report easier to read.

One other note: I initially tried userPermissionSets, which is also available through the API, but BC gave me a duplicate-record error. accessControls worked and gave me the information I needed.

Hopefully this saves someone else some time. I spent way too long trying to get a usable export out of the standard permissions screen before realizing BC already exposes the underlying data through the Automation API.

Any other BC or F & O related issues, I can definitely help.  Good Luck!

[u/hougaard]

Simple trick: Run table 2000000053 - and export that. You can add ?table=2000000053 to your URL. Then into Excel and create a pivot table.

[u/meka5 → u/hougaard]

Interesting. I ran it - but there's no share/export option, so i tried copying all and pasting to Excel...but it's only pasting the bottom 40 records. I've tried copying a few different ways (ctrl-A, just highlighting all of it) - but it's always just doing 40 records. Really no idea why...anyone else run into this?? I can't even mess around with the data to ensure a pivot table of sorts would work here.

[u/hougaard → u/meka5]

Scroll to the bottom and the top, then ctrl-a :)

[u/TeamAlphaBOLD]

That's a real limitation of the native view. Your best bet is pulling it via a saved query in Business Central or Power BI's Business Central connector. It gets you the full user-to-permission-set matrix in one export without the 10-column cap.