July 9, 2010
While Query the FIM Service Database at the SQL layer is not supported by Microsoft I had an issue the other day where I couldn’t find what object had a conflicting SID that was preventing the update of another user. I could see in the error detail that it referenced the ObjectSID attribute. So I created this script and replaced the binary value down below with the SID of the object I was looking for.
This SQL Script will find any person object that has any binary attribute with this value in it.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from fim.Objects where [ObjectKEY] IN (
select ObjectKey from fim.ObjectValueBinary where ObjectKey in
select ObjectKey from fim.Objects o
where o.ObjectTypeKey = (SELECT oti.[key] from fim.ObjectTypeInternal oti where Name = ‘person’)
and ValueBinary = 0x010200000000000916000000C83BFC025A1C2A4F9175596438570000