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
