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.
Searching an entire database for a Guid or Unique Identifier can be a bit of a tricky proposition. However a little bit of using T-SQL to generate T-SQL and viola DECLARE @GUIDHunted nvarchar(60) SET @GUIDHunted = ‘0A24EC0C-65EE-4519-89DF-ABD3DD24F7EF’ SELECT *, ‘UNION ALL SELECT '’' + s.name + ‘.’ + ao.name + ‘'’, count(*) FROM ' + s.name +'.[' + ao.name + ‘] WHERE ' + ac.name + ' = '’' + @GuidHunted + ''''
