• I notice you've rewritten part of the stored procedure, USP_ICMExtendedSummaryReport_Temp, now has:

    ...

    from dbo.EOS_Reports c with (nolock)

    INNER JOIN #TempUserAccess T on T.AccessID = c.OrgPathID AND T.AccessType = 'NONSHARED'

    where

    (c.OrgPathID in (select AccessID from #TempUserAccess where AccessType = 'NONSHARED')

    or c.SharedOrgPathID in (select AccessID from #TempUserAccess where AccessType = 'SHARED')

    or c.EntityInstanceID IN (select distinct AccessID from #TempUserAccess where AccessType = 'TEAM'))

    This definitely reduces the number of table scans on EOS_Reports since you only reference it once instead of 3 times in UNIONs, but having all those OR conditions will probably make it more likely to keep the table scan on EOS_Reports. The first of the three condition in the OR also seems to match the second condition on your INNER JOIN #TempUserAccess.

    Have you compared the 2 different inexes on #TempUserAccess that you have? I notice a commented out version is:

    CREATE NONCLUSTERED INDEX [NCI_TempAccessType] ON #TempUserAccess ([AccessType]) INCLUDE ([AccessID])

    I'm thinking that or

    CREATE NONCLUSTERED INDEX [NCI_TempAccessType] ON #TempUserAccess (AccessType,AccessID)

    may perform better than indexing on AccessID first but it's difficult to tell without knowing the cardinality of data within the temp table for each AccessType.