After an upgrade had been performed there were hundreds of active policies whose targets were not getting updated when the Delta Resource Membership update schedule ran. The targets could not be edited or manually updated because they were grayed out.
If the target was deleted and created again the problem was fixed for the policy. But there were hundreds affected.
Found in 7.5.1 and 7.6. Possibly others.
The problem is rare so it is not understood why/how this condition occurred; but it is believed to be related to timing in communications between the SMP and the database server during an upgrade.
Within that window, or some other event the following had occurred (one or both).
The following query will return the names of the affected policies, and the count of targets per policy that are problematic.
select distinct p.Name as Policy, COUNT(*) as [Affected Targets]
from ItemAppliesTo iat
join Item p on p.GUID = iat.ItemGuid
join Item i on i.GUID = iat.ResourceTargetGuid
left join ResourceTargetOwnerTrustees rot on rot.ResourceTargetGuid = iat.ResourceTargetGuid
left join SecurityTrustee st on st.GUID = rot.TrusteeGuid
where rot.ResourceTargetGuid is null or st.GUID is null
group by p.Name
order by p.Name
Run the SQL script attached to this article. The script will do the following two procedures.
Note: It is good practice and highly recommended that, before running any SQL script against a database, a backup of the database should be created first.