Jan 14

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

Leave a Reply

Get Adobe Flash player