web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Management Reporter SQ...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Management Reporter SQL Table Information for Security

(3) ShareShare
ReportReport
Posted on by texasbbqribs 164
Greetings. 
 
We'd like to create a SQL query that displays the permissions for each user. I am having a problem finding the SQL table that stores the permissions for a user. I'm sure it's tied to the assigned role. Regardless, I cannot find the table that stores the information. 
 
For example, I would like to be able to display the permissions in this window for this user. We're trying to get all users' permissions. 
 
Categories:
Microsoft Dynamics GP
I have the same question (0)
  • Suggested answer
    Lisa at AonC.com Profile Picture
    Lisa at AonC.com 1,062 Super User 2026 Season 1 on at
    Interesting question ... I don't have any clients who have set any security deeper than the pre-defined roles.

    When we need to report on those roles, I use the following script (on the ManagementReporter database):

    --MR UserNames, along with DomainNames, MR Role assignment and MR Group assignment 
    SELECT a.username, 
           b.[name]        AS DomainName, 
           CASE a.roletype 
             WHEN 5 THEN 'Admin' 
             WHEN 4 THEN 'Designer' 
             WHEN 3 THEN 'Generator' 
           END             RoleDescr, 
           d.[name]        AS GroupID, 
           d.[description] AS GroupName 
    FROM   reporting.securityuser a (nolock) 
           JOIN reporting.securityprincipal b (nolock) 
             ON a.userid = B.id 
           LEFT JOIN reporting.securitygroupuser c (nolock) 
                  ON a.userid = c.userid 
           LEFT JOIN reporting.securityprincipal d (nolock) 
                  ON c.groupid = d.id 
    ORDER  BY d.[description], 
              a.roletype
     
    I see other tables with names that begin with reporting.security that may have the information you are looking for.  (Since I don't have any data at that level, I'm not certain what you will find as you review the tables.)

    Note:  The current table structure that includes a period in the name was a change from early versions of MR.  If you are on a very old version, you may see different table naming structure but while the exact names changed, I recall the naming logic followed the same pattern.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 659

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 465 Super User 2026 Season 1

#3
Syed Haris Shah Profile Picture

Syed Haris Shah 304 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans