Hi!
This is perhaps posted in the incorrect forum, but my hope is that someone could at least point me in the right direction in this case.
I have a customer that hosts their own on-premise CRM environments (Microsoft Dynamics 365 v9.0.22.8). The customer has several business units in their production environment. For some reason there is a specific SQL query that is run for users that do not have full read privilege (i.e. organization-level read access) on the Case entity (incident) whenever they open a Contact record in the GUI. If the user has full read privilege this query is never executed. The problem is that this query is executed quite a lot and depending on the size of the PrincipalObjectAccess table (which the query is using) this has become a performance issue. Sometimes this query runs for 0.5 to 1 minute. And it is run very often in a typical business day. Below is an example of such a query:
with "incident0Security"
as (
select Title as "Title"
,CaseOriginCode as "CaseOriginCode"
,IncidentId as "IncidentId"
,ProcessId as "ProcessId"
,VersionNumber as "VersionNumber"
,ModifiedOn as "ModifiedOn"
,StateCode as "StateCode"
,OwningBusinessUnit as "OwningBusinessUnit"
from [Incident] as "incident0"
where "incident0".IncidentId in (
select ObjectId
from fn_POARetrieveMultipleNonHierarchy(@SystemUserId0, @ObjectTypeCode0)
)
union
select Title as "Title"
,CaseOriginCode as "CaseOriginCode"
,IncidentId as "IncidentId"
,ProcessId as "ProcessId"
,VersionNumber as "VersionNumber"
,ModifiedOn as "ModifiedOn"
,StateCode as "StateCode"
,OwningBusinessUnit as "OwningBusinessUnit"
from [Incident] as "incident0"
where "incident0".OwningBusinessUnit in (@buId00)
)
select top 9 "incident0".Title as "title"
,"incident0".CaseOriginCode as "caseorigincode"
,"incident0".IncidentId as "incidentid"
,"incident0".ProcessId as "processid"
,convert(bigint, "incident0".VersionNumber) as "versionnumber"
,"incident0".ModifiedOn as "modifiedon"
,convert(bigint, "processidworkflowworkflowid".VersionNumber) as "processidworkflowworkflowid.versionnumber"
from incident0Security as "incident0"
left outer join Workflow as "processidworkflowworkflowid" on ("incident0".ProcessId = "processidworkflowworkflowid".WorkflowId)
where ("incident0".StateCode = @StateCode0)
order by "incident0".ModifiedOn desc
,"incident0".IncidentId asc.
I would like to know why this query needs to be executed whenever the user opens the Contact form and does not have organizational-level read privilege on the Case entity and whether there is a way to avoid this query from being executed without having to change the security settings of the environment (i.e., without giving all users full read privilege on the Case entity). Any ideas or suggestions on what can be done to resolve this issue would be greatly appreciated.
I should also mention that I have looked into the orphan-problem in table PrincipalObjectAccess (identified in CRM 2011), but the script Microsoft provided for that issue does not find any such records in the PrincipalObjectAccess table in my customer's database.
Best regards,
Henrik Svensson
