Symptoms:
- Field Security Profile Does Not List Enabled Custom Fields
- Certain forms would show a lock symbol with masked contents ‘*****’
- Field Security enable and disable options cannot be saved without throwing errors
- 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:
- 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
- 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
- Settings > Data management > Imports > click on any item > see error code 0x80040217 (this has happened since the move)
Error Message:
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
Categories: