I recently found myself needing a function to turn SDDL format SIDs into varbinary using only SQL.

I couldn’t find any examples of going in that direction so for the benefit of anyone who finds themselves stuck in that position (your poor sod) here is the Function I hacked together. I’m sure there are better ways and I kept the table variable in there as it helped me check what I had done so far.

create function dbo.ConvertSSDLToVarBinary (@SIDSSDL nvarchar(max))

Returns varbinary(max)

Begin

declare @SplitTable TABLE

(

Id int identity(1,1) primary key,

Data nvarchar(max),

HexString nvarchar(max)

)

Declare @SplitOn nvarchar(1)

Set @SplitOn =’-‘

Declare @Cnt int

Set @Cnt = 1

Declare @tmpString nvarchar(max)

While (Charindex(@SplitOn,@SIDSSDL)>0)

Begin

Set @tmpString = ltrim(rtrim(Substring(@SIDSSDL,1,Charindex(@SplitOn,@SIDSSDL)-1)));

IF @Cnt > 1 –Lose the S

BEGIN

IF @Cnt = 2 — First is a single digit 1 for 1 byte

BEGIN

Insert Into @SplitTable (data,HexString)

Select @tmpString,

CONVERT(NVARCHAR(MAX),CONVERT(VarBinary(1),CONVERT(INT,@tmpString),0),2)

END

ELSE IF @Cnt = 3 –48 bit BIGENDIAN, 6 for 6 bytes

BEGIN

Insert Into @SplitTable (data,HexString)

Select @tmpString,

CONVERT(NVARCHAR(MAX),CONVERT(VarBinary(6),CONVERT(BIGINT,@tmpString),0),2)

END

ELSE

BEGIN — 32bit LittleEndian so reverse, 4 for 4 bytes

Insert Into @SplitTable (data,HexString)

Select @tmpString,

CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY,REVERSE(CONVERT(VarBinary(4),CONVERT(BIGINT,@tmpString),3))),2)

END

END

Set @SIDSSDL = Substring(@SIDSSDL,Charindex(@SplitOn,@SIDSSDL)+1,len(@SIDSSDL))

Set @Cnt = @Cnt + 1

End

IF Len(@SIDSSDL) <> 0

BEGIN

Insert Into @SplitTable (data,HexString)

Select Data = ltrim(rtrim(@SIDSSDL)),

CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY,REVERSE(CONVERT(VarBinary(4),CONVERT(BIGINT,@SIDSSDL),3))),2)

END

— We now have a table with all of the converted items

— Now need to construct from the table the string for the hex including the special item that

— tracks the number of dashes -2

declare @NumberOfDashesMinus2 int

set @NumberOfDashesMinus2 = @Cnt -3 –Our count starts at 1

set @Cnt = 1

declare @FullHexString nvarchar(Max)

set @FullHexString = ‘0x’

declare @CurrentString nvarchar(Max)

select @CurrentString = HexString from @SplitTable where ID = @Cnt

while @CurrentString IS NOT NULL

BEGIN

set @FullHexString = @FullHexString + @CurrentString

IF @Cnt = 1

BEGIN

set @FullHexString = @FullHexString + CONVERT(NVARCHAR(MAX),CONVERT(VarBinary(1),CONVERT(INT,@NumberOfDashesMinus2),0),2)

END

select @CurrentString = NULL

set @Cnt = @Cnt + 1

select @CurrentString = HexString from @SplitTable where ID = @Cnt

END

return convert(VarBinary(max),@FullHexString,1)

End

GO