Fixed. Well maybe....
We got both the MR error eliminated and also the DataMart refreshed.
we had a great deal of trouble getting the initial DataMart integration to complete.
Turns out the MR error was preventing the DataMart from completing its Initial integration.
What we found
The AX/MR service account needed to have the SecurityAdmin role applied, it only had SystemAdmin and SystemUser.
Since I use my named account to run the MR console I Also applied this to my named account.
The client was doing a security audit. I suspect they did not know any better and so...
We also needed to temporarily disable the Trigger that was causing the Error message. The trigger was tripping and threw up the error to the event logs.
“Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]' due to trigger [Reporting].[SecurityPrincipal_Insert_UniqueName]”
Disabling the trigger appears to have accomplished two things:
The Datamart was able to complete it Initial integration.
And it allowed several user accounts to be populated into the MR database (SecurityPrinciple) table.
So it would appear the Error was preventing the MR users list from updating.
select * from [managementreporter].[reporting].SecurityPriciple
We later re-enable the trigger and we are getting no more MR errors.
In Report Designer the list of users should now agree with the role memberships in AX and with the SecurityPrinciple table.
We are concluding that there must have been something that tripped the trigger. Maybe a duplicate entry. Maybe caused by permissions issues (axprod was unable to complete some function correctly?).
We will have the client create a new AX user, and assign them to some report writer role or their new custom roles and see if the table update takes place. And see if the Trigger again throws an MR Error.
Got lots of help with this so thanks to everyone!!!
-Damian