What Managed Delivery Policies use a specified user account

Products

• Client Management Suite
• IT Management Suite

Issue/Introduction

We have some Managed Software Delivery Policies that run as a specified user.  We need help identifying which Managed Software Delivery Policies are using a specific user account.  Is there a report to show us the users specified in the MSD Policies?

Resolution

The following SQL will provide a list of Policy names to edit as needed.
Insert the username that requires a password edit or requires change where indicated:

select DISTINCT Policy.name
from itemversiondata ivd
join ItemVersions iv on iv.VersionGuid = ivd.VersionGuid
join item task on task.Guid = iv.ItemGuid
join ItemReference ir on ir.ChildItemGuid = task.Guid
join item Policy on Policy.guid = ir.ParentItemGuid
where ivd.State like '%<username>admin</username>%'