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 / Microsoft Dynamics AX (Archived) / Error in Management Re...
Microsoft Dynamics AX (Archived)

Error in Management Reporter Service Log - Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]

(0) ShareShare
ReportReport
Posted on by Community Member

Hi all

I have the following error showing in Management Reporter Service Log.  Running AX 2012, and Management Reporter CU13.

Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]' due to trigger '[Reporting].[SecurityPrincipal_Insert_UniqueName]'.
The transaction ended in the trigger. The batch has been aborted.

Can anyone point me to what this issue may relate to please and how I might fix it?

Thanks

James

*This post is locked for comments

I have the same question (0)
  • Mea_ Profile Picture
    Mea_ 60,286 on at

    Hi James Terrington,

    Please refer to this old thread with similar question community.dynamics.com/.../182625

  • Community Member Profile Picture
    Community Member on at

    Hi James,


    We are facing the exact issue, please let us know how you overcame this issue. Thanks in Advance

  • Community Member Profile Picture
    Community Member on at

    Hey James,

    We have exactly the same problem.  Same MR cu13.  Same error.

    Aside from the error message were there any functional problems as far as you could tell?

    Did you have any luck figuring out how to correct this?

    I tried dropping and rebuilding the datamart.  And the error still persists.

    This is the Trigger in table Reporting.SecurityPrinciple at issue

    USE [ManagementReporter]

    GO

    /****** Object:  Trigger [Reporting].[SecurityPrincipal_Insert_UniqueName]    Script Date: 3/14/2018 6:14:02 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Reporting].[SecurityPrincipal_Insert_UniqueName]

    ON [Reporting].[SecurityPrincipal]

    FOR INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @DuplicateName nvarchar(255)

    SELECT TOP 1 @DuplicateName = i.[Name] FROM inserted i INNER JOIN [Reporting].[SecurityPrincipal] sp ON sp.[Name] = i.[Name] AND sp.[ID] <> i.[ID] AND sp.[IsDeleted] = 0

    IF (@DuplicateName IS NOT NULL) BEGIN

    RAISERROR(52601, 16, 0, '[Reporting].[SecurityPrincipal]', '[Reporting].[SecurityPrincipal_Insert_UniqueName]') --This needs to be a THROW for azure

    ROLLBACK TRANSACTION

    END

    END

    GO

    Thanks

    -Damian

  • Community Member Profile Picture
    Community Member on at

    Hey James,

    We have exactly the same problem.  Same MR cu13.  Same error.

    Aside from the error message were there any functional problems as far as you could tell?

    Did you have any luck figuring out how to correct this?

    I tried dropping and rebuilding the datamart.  And the error still persists.

    This is the Trigger in table Reporting.SecurityPrinciple at issue

    USE [ManagementReporter]

    GO

    /****** Object:  Trigger [Reporting].[SecurityPrincipal_Insert_UniqueName]    Script Date: 3/14/2018 6:14:02 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [Reporting].[SecurityPrincipal_Insert_UniqueName]

    ON [Reporting].[SecurityPrincipal]

    FOR INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @DuplicateName nvarchar(255)

    SELECT TOP 1 @DuplicateName = i.[Name] FROM inserted i INNER JOIN [Reporting].[SecurityPrincipal] sp ON sp.[Name] = i.[Name] AND sp.[ID] <> i.[ID] AND sp.[IsDeleted] = 0

    IF (@DuplicateName IS NOT NULL) BEGIN

    RAISERROR(52601, 16, 0, '[Reporting].[SecurityPrincipal]', '[Reporting].[SecurityPrincipal_Insert_UniqueName]') --This needs to be a THROW for azure

    ROLLBACK TRANSACTION

    END

    END

    GO

    Thanks

    -Damian

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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

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!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Basit Profile Picture

Basit 1

#1
GL-01081504-0 Profile Picture

GL-01081504-0 1

#1
Roya Profile Picture

Roya 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans