Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

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

Posted on by Microsoft Employee

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

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Error in Management Reporter Service Log - Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]

    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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Error in Management Reporter Service Log - Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]

    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 Microsoft Employee on at
    RE: Error in Management Reporter Service Log - Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]

    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 Microsoft Employee on at
    RE: Error in Management Reporter Service Log - Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]

    Hi James,


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

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Error in Management Reporter Service Log - Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]

    Hi James Terrington,

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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templates⚡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans