Joining on objectSID or using objectSID in a WHERE Clause

This one drove me crazy for the better part of a day.
When SQL Server shows you an objectSID it looks something like this:

0x010500000000000515000000317B347F75771D5BB254B44007080000

If you take that string and type something like:

SELECT * FROM viewADEmployees WHERE objectSID = 0x010500000000000515000000317B347F75771D5BB254B44007080000

It works just fine.

But if you take that string and stuff it in a VARCHAR (or any other type you think might be appropriate) you get an empty result set.

So I thought, duh, I need to cast it to the same type as objectSID. It only took a little while to figure out that objectSID is of type VARBINARY. It turns out, however, that simply casting the string to VARBINARY does NOT work! In fact I could not find any CAST or CONVERT that did the trick. I performed more casts than the entire fishing population on opening day of trout season.

Google was of no help either. I was on my own on this one.

I finally stepped back reasoned it out:

1. objectSID is VARBINARY
2. I need to convert my comparison value to VARBINARY
3. I should create a user function that takes a string representation of a series of hex byte values
4. each two-character pair of hex digits reprsents a single binary byte
5. I need to build up a VARBINARY value by successively converting the pairs to integers and then adding them to my VARBINARY return value

This is my resulting function which took a suprisingly short period of time to conceive, write and debug :

CREATE FUNCTION dbo.fncFormatSID (@vchFromSID VARCHAR(300))
RETURNS VARBINARY(85)
AS
BEGIN
DECLARE @binOut VARBINARY(85);
DECLARE @intLen INT;
DECLARE @intIndex INT;
DECLARE @intLow INT;
DECLARE @intHigh INT;
DECLARE @vchLow VARCHAR(1);
DECLARE @vchHigh VARCHAR(1);

SELECT @intLen = LEN(@vchFromSID)
SELECT @intIndex = 2

WHILE (@intIndex < @intLen)
BEGIN

SELECT @vchHigh = SUBSTRING(@vchFromSID, @intIndex, 1)
SELECT @vchLow = SUBSTRING(@vchFromSID, @intIndex + 1, 1)

IF(@vchLow >= '0' AND @vchLow <= '9')
BEGIN
SELECT @intLow = ASCII(@vchLow) - ASCII("0");
END

IF(@vchHigh >= '0' AND @vchHigh <= '9')
BEGIN
SELECT @intHigh = ASCII(@vchHigh) - ASCII("0");
END

IF(@vchLow >= 'A' AND @vchLow <= 'F')
BEGIN
SELECT @intLow = ASCII(@vchLow) - ASCII("A") + 10;
END

IF(@vchHigh >= 'A' AND @vchHigh <= 'F')
BEGIN
SELECT @intHigh = ASCII(@vchHigh) - ASCII("A") + 10;
END

IF @binOut IS NULL
BEGIN
SELECT @binOut = CONVERT(VARBINARY(1), (@intHigh * 16) + @intLow) ;
END
ELSE BEGIN
SELECT @binOut = @binOut + CONVERT(VARBINARY(1), (@intHigh * 16) + @intLow) ;
END

SELECT @intIndex = @intIndex + 3

END /* While */


RETURN(@binOut);

END