Microsoft Dynamics 365: System Administrator Profile Corruption Problem

Symptoms:
  1. Field Security Profile Does Not List Enabled Custom Fields
  2. Certain forms would show a lock symbol with masked contents ‘*****’
  3. Field Security enable and disable options cannot be saved without throwing errors
  4. Data management imports fail with error code 0x80040217

Although this issue has been known to affect Microsoft Dynamics 365, the cloud version of Microsoft Products as well as ‘Microsoft Dynamics 365 Customer Engagement’ or on-premise version, our blog shall focus on the latter as the former has been patched by Microsoft since October 2020.

This may occur after Microsoft Dynamics has been upgraded to version 9.0 from earlier versions, certain forms would show a lock symbol with masked contents ‘*****’ when viewed via the GUI web portal. Further more, these are the steps to reproduce and observe other symptoms of this issue, after login to CRM as SystemAdmin:

  1. Settings > Customizations > Customize the system > Components > Entities > Lead > Fields > change View = Custom > double-click on a customized field (e.g. ‘crm_internalnotes’)

    A new window appears, Under the ‘General’ tab > any attempt to toggle to ‘disable’ or ‘enable’ the ‘Field Security’ will generate the ‘Field Security Profile Error’ code ‘-2147158771’. Sometimes, the radio button is even grayed-out so that no changes can be made to that field security
  2. Settings > Customizations > Customize the system > Components > Field Security Profiles > double click on a a field (e.g. ‘common’) > field permissions > [custom field name] would not appear on the list as expected
  3. Settings > Data management > Imports > click on any item > see error code 0x80040217 (this has happened since the move)

Error Message:

Field Security Profile Error
Activity ID: [ACTIVITYGUID]
Timestamp: Sun Nov 01 2020 05:14:05 (Pacific Standard Time)
Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Caller [SYSTEMUSERID] does not have full permissions on the attribute '[FIELDNAME]' to unsecure the attributeDetail: 
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ActivityId>[ACTIVITYGUID]</ActivityId>
  <ErrorCode>-2147158771</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>Caller [SYSTEMUSERID] does not have full permissions on the attribute [ACTIVITYGUID] to unsecure the attribute</Message>
  <Timestamp>2020-11-01</Timestamp>
  <ExceptionRetriable>false</ExceptionRetriable>
  <ExceptionSource i:nil="true" />
  <InnerFault i:nil="true" />
  <OriginalException i:nil="true" />
  <TraceText i:nil="true" />
</OrganizationServiceFault>
Resolution:
Backup database and prepare to restore:
  • Sample Backup T-SQL
    BACKUP DATABASE TEST_MSCRM
    TO DISK = '\\BackupServerUNCPath\TEST_MSCRM.bak' WITH COMPRESSION,COPY_ONLY
    GO
  • Sample Restore T-SQL
    USE master
    GO
    ALTER DATABASE TEST_MSCRM
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE [TEST_MSCRM] FROM DISK = N'\\BackupServerUNCPath\TEST_MSCRM.bak' WITH FILE = 1,
    MOVE N'mscrm' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_MSCRM.mdf',
    MOVE N'mscrm_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_MSCRM.ldf',
    NOUNLOAD, REPLACE, STATS = 5;
    ALTER DATABASE [TEST_MSCRM] SET MULTI_USER
    GO
Run this T-SQL:

Source: courtesy of my anonymous friend from Microsoft

USE TEST_MSCRM
GO
declare @testOnlyDoNotCommit int = 1; -- Change this value to 0 to apply the fix
DECLARE @SysAdminProfileId uniqueidentifier = '572329C1-A042-4E22-BE47-367C6374EA45'
DECLARE @SecuredAttributeMissingInSysAdminProfile TABLE(AttributeName nvarchar(100), ObjectTypeCode int, AttributeDependencyNodeId uniqueidentifier, CanBeSecuredForCreate bit, CanBeSecuredForRead bit, CanBeSecuredForUpdate bit)
DECLARE @NonSecuredAttributeInSysAdminProfile TABLE(FieldPermissionId uniqueidentifier, AttributeName nvarchar(100), ObjectTypeCode int)

-- Identify field permissions missing in System Administrator profile for secured attributes.
INSERT INTO @SecuredAttributeMissingInSysAdminProfile (AttributeName, ObjectTypeCode, AttributeDependencyNodeId, CanBeSecuredForCreate, CanBeSecuredForRead, CanBeSecuredForUpdate )
SELECT a.LogicalName, e.ObjectTypeCode, dn.DependencyNodeId, a.CanBeSecuredForCreate, a.CanBeSecuredForRead, a.CanBeSecuredForUpdate 
  FROM AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
    LEFT OUTER JOIN FieldPermission fp ON (fp.EntityName = e.ObjectTypeCode AND fp.AttributeLogicalName = a.LogicalName AND FieldSecurityProfileId = @SysAdminProfileId)
    LEFT JOIN DependencyNodeBase dn ON dn.ObjectId = a.AttributeId
 WHERE a.IsSecured = 1 AND fp.AttributeLogicalName IS NULL
 
-- Identify invalid field permissions defined in System Administrator profile for non-secured attributes.
INSERT INTO @NonSecuredAttributeInSysAdminProfile (FieldPermissionId, AttributeName, ObjectTypeCode)
SELECT fp.FieldPermissionId, fp.AttributeLogicalName, fp.EntityName
  FROM FieldPermission fp LEFT OUTER JOIN (
        SELECT a.LogicalName, e.ObjectTypeCode
          FROM  AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
         WHERE a.IsSecured = 1
    ) ae ON (fp.EntityName = ae.ObjectTypeCode AND fp.AttributeLogicalName = ae.LogicalName)
 WHERE FieldSecurityProfileId = @SysAdminProfileId AND ae.LogicalName IS NULL

IF EXISTS(SELECT 1 FROM @SecuredAttributeMissingInSysAdminProfile UNION SELECT 1 FROM @NonSecuredAttributeInSysAdminProfile)
 BEGIN
    SELECT FORMATMESSAGE('@testOnlyDoNotCommit = %d', @testOnlyDoNotCommit)
    -- Output missing permissions.  
    SELECT 'Field permissions missing in System Administrator profile for secured attributes.'
    SELECT * FROM @SecuredAttributeMissingInSysAdminProfile 
    -- Output invalid permissions.
    SELECT 'Invalid field permissions defined in System Administrator profile for non-secured attributes.'
    SELECT * FROM @NonSecuredAttributeInSysAdminProfile
    BEGIN TRAN
     BEGIN TRY    
        DECLARE @BasicSolutionId uniqueidentifier = '25A01723-9F63-4449-A3E0-046CC23A2902'
        DECLARE @RecreatedFieldPermission TABLE(FieldPermissionId uniqueidentifier, AttributeLogicalName nvarchar(50), EntityName int, DependencyNodeId uniqueidentifier)
        -- Create missing System Administrator field permissions. 
        INSERT INTO FieldPermissionBase (FieldPermissionIdUnique, 
                         ComponentState,
                         SolutionId,
                         CanRead,
                         SupportingSolutionId,
                         CanCreate,
                         FieldSecurityProfileId,
                         EntityName,
                         FieldPermissionId,
                         IsManaged,
                         OverwriteTime,
                         CanUpdate,
                         AttributeLogicalName)
        OUTPUT inserted.FieldPermissionId, inserted.AttributeLogicalName, inserted.EntityName, NEWID() INTO @RecreatedFieldPermission
        SELECT NEWID(), 
             0,
             @BasicSolutionId,
             CASE WHEN a.CanBeSecuredForRead = 0 THEN 0 ELSE 4 END,
             NULL,
             CASE WHEN a.CanBeSecuredForCreate = 0 THEN 0 ELSE 4 END,
             @SysAdminProfileId,
             a.ObjectTypeCode,
             NEWID(),
             1,
             0,
             CASE WHEN a.CanBeSecuredForUpdate = 0 THEN 0 ELSE 4 END,
             a.AttributeName
        FROM @SecuredAttributeMissingInSysAdminProfile a
        SELECT * FROM @RecreatedFieldPermission
        -- Create dependency. 
        DECLARE @SystemAdminProfileDependencyNodeId uniqueidentifier
        SELECT @SystemAdminProfileDependencyNodeId = DependencyNodeId FROM DependencyNodeBase WHERE ObjectId = @SysAdminProfileId
        INSERT INTO DependencyNodeBase (BaseSolutionId, ObjectId, TopSolutionId, ParentId, IsSharedComponent, DependencyNodeId, ComponentType)
        OUTPUT inserted.*
        SELECT @BasicSolutionId, rfp.FieldPermissionId, @BasicSolutionId, '00000000-0000-0000-0000-000000000000', 0, rfp.DependencyNodeId, 71 
        FROM @RecreatedFieldPermission rfp
        INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
        OUTPUT inserted.*
        SELECT rfp.DependencyNodeId, NEWID(), 1, a.AttributeDependencyNodeId
        FROM @RecreatedFieldPermission rfp JOIN @SecuredAttributeMissingInSysAdminProfile a ON rfp.EntityName = a.ObjectTypeCode AND rfp.AttributeLogicalName = a.AttributeName 
        WHERE a.AttributeDependencyNodeId IS NOT NULL
        INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
        OUTPUT inserted.*
        SELECT rfp.DependencyNodeId, NEWID(), 1, @SystemAdminProfileDependencyNodeId
        FROM @RecreatedFieldPermission rfp
        WHERE @SystemAdminProfileDependencyNodeId IS NOT NULL
        -- Delete invalid System Administrator field permissions.
        DELETE FROM FieldPermissionBase
        OUTPUT deleted.*
        WHERE FieldPermissionId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        -- Delete invalid dependencies.
        DELETE FROM DependencyBase
        OUTPUT deleted.*
        WHERE DependentComponentNodeId IN (
            SELECT DependencyNodeId FROM DependencyNodeBase WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        )
        DELETE FROM DependencyNodeBase
        OUTPUT deleted.*
        WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
        IF (@testOnlyDoNotCommit = 1)
         BEGIN
            ROLLBACK TRAN
            SELECT 'TRANSACTION ROLLEDBACK'
         END
        ELSE
         BEGIN
            -- Recalculate PrincipalAttributeAccessMap for System Administrator profile members.
            DECLARE @principalUserIds EntityIdCollection
            DECLARE @principalTeamIds EntityIdCollection
            INSERT INTO @principalUserIds SELECT SystemUserId FROM SystemUserProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
            INSERT INTO @principalTeamIds SELECT TeamId from TeamProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
            EXEC p_PrincipalAttributeAccessMapReinitBulk @principalUserIds, @principalTeamIds, 1
            COMMIT TRAN 
            SELECT 'TRANSACTION COMMITED'
         END
     END TRY
     BEGIN CATCH
        SELECT 'ERROR while executing script. Transaction rolled back. ' + ERROR_MESSAGE()
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
            SELECT 'TRANSACTION ROLLEDBACK'
        END
     END CATCH 
 END
ELSE
 BEGIN
    SELECT 'System administrator profile is healthy. Did not find any corruption.'
 END

Leave a Reply

Your email address will not be published. Required fields are marked *