Active Directory Guid CRM vs. other system

Question

I have two Microsoft systems (CRM and Project Server) that store the active directory user GUID differently. The first part of it is scrambled, but it has the exact same letters and numbers in it.

NOTICE in bold the similarites
84CCB872-37A1-4F89-BF73-4E1C7891E9ED
72B8CC84-A137-894F-BF73-4E1C7891E9ED

and if you compare the numbers in the last part of the 1st section: 4F89 vs. 894F (same numbers and letters, different ordering)

Could someone help me with the SQL logic of 'unscrambling' one of them? My goal is to match users in both systems based on AD Guids, since that is an attribute that is common in both systems for the user.

Answer 1

I dont know why you'd like to do that but as i see it, the numbers are just switched?

4f89 - 894f


84CCB872
you have to switch first and last

72CCB884 voila

and then switch the rest
72B8CC84? Does that do the trick?
finished.

Answer 2

Thanks Michael,

Yeah they need to be switched. I am looking for a sql statement that will take one of them and make it like the other.

The reason I want to join on the active directory guid in both systems is that the user id in each system is different.

Thanks.

NovaCRM

Answer 3

Nova, the below function should do the trick for you. It will convert them in sets of 2. Granted, this is a hard coded solution, but it works

---

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Carlton Colter
-- Create date: 10/13/2009
-- Description:	Guid - Matcher
-- =============================================
CREATE FUNCTION ConvertActiveDirectoryGuid 
(
	-- Add the parameters for the function here
	@Start uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
	IF (@Start IS NULL) RETURN NULL

	DECLARE @StartString varchar(50)
	SET @StartString = CAST(@Start as varchar(50))
	
	RETURN CAST(
	      (Substring(@StartString,7,2) 
         + Substring(@StartString,5,2)
         + Substring(@StartString,3,2)
         + Substring(@StartString,1,2)
         + '-'
         + Substring(@StartString,12,2)
         + Substring(@StartString,10,2)
         + '-'
         + Substring(@StartString,17,2)
         + Substring(@StartString,15,2)
         + Substring(@StartString,19,18)
          ) as uniqueidentifier)
END
GO

---

To test the above function, you can use:

DECLARE @Start uniqueidentifier, @Match uniqueidentifier
SET @Match = '84CCB872-37A1-4F89-BF73-4E1C7891E9ED'
SET @Start = '72B8CC84-A137-894F-BF73-4E1C7891E9ED'
PRINT @Match
PRINT dbo.ConvertActiveDirectoryGuid(@Start)

---

You can place that function on either db or a custom db that links the two and does the integration. I'm sure Microsoft would prefer that you don't modify their databases.

Answer 4

this works great, thank you!