MIM Portal Groups whose displayedOwner isn't among the Owners

In the MIM Portal it will create issues if you have a group whose displayedOwner isn't among the objects in the multivalued reference attribute Owner. Querying this through XPath is just about impossible so here is the SQL query to do it.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO​

USE FIMService​

GO​

​

SELECT DOwn.*

FROM (​

SELECT groupObjID = G.[objectID]

           , GroupDisplayName = GAOVS.ValueString

           , userDisplayName= UAOVS.ValueString

           , UserObjID =  U.[objectid]​

FROM [fim].[ObjectValueReference] GOVR​

JOIN [fim].[ObjectValueString] GAOVS​

        ON GOVR.ObjectKey = GAOVS.ObjectKey​

JOIN [fim].[Objects] G​

          ON G.ObjectKey = GOVR.ObjectKey​

JOIN [fim].[ObjectValueString] UAOVS​

           ON GOVR.ValueReference = UAOVS.ObjectKey​

JOIN [fim].[Objects] U​

          ON U.ObjectKey = GOVR.ValueReference​

WHERE GOVR.[AttributeKey] =65 -- DisplayedOwner​

     AND UAOVS.[AttributeKey] = 1 

    AND GAOVS.[AttributeKey] = 1 -- DisplayedName​

) DOwn​ --DisplayedOwners

LEFT JOIN​

(​

SELECT groupObjID = G.[objectID]

              , GroupDisplayName  = GAOVS.ValueString

             , userDisplayName = UAOVS.ValueString

             , UserObjID =  U.[objectid]​

FROM [fim].[ObjectValueReference] GOVR​

JOIN [fim].[ObjectValueString] GAOVS​

        ON GOVR.ObjectKey = GAOVS.ObjectKey​

JOIN [fim].[Objects] G​

         ON G.ObjectKey = GOVR.ObjectKey​

JOIN [fim].[ObjectValueString] UAOVS​

          ON GOVR.ValueReference = UAOVS.ObjectKey​

JOIN [fim].[Objects] U​

          ON U.ObjectKey = GOVR.ValueReference​

WHERE   GOVR.[AttributeKey] =138 -- Owner​

        AND UAOVS.[AttributeKey] = 1 

        AND GAOVS.[AttributeKey] = 1 --DisplayedName​

) Own​ -- Owners

On Down.gObjID = Own.gObjID 

    AND Down.UObjID = Own.UObjID​

WHERE Own.UObjID IS NULL​

order by DOwn.GacctName