Hello,

Thanks for this function. I was in need for the reverse (OS string SID converted to binary sid --> and then user name), but this function was good for explaining how the conversion needs to be done. For those who are looking for the reverse SQL function see below. Reviews/additions are welcome since this is the first release 🙂

CREATE FUNCTION fn_StringToSID

(

@xStrSid VARCHAR(100)

)

RETURNS VARBINARY(100)

AS

BEGIN

DECLARE @xBinSid VARBINARY(100)

SET @xBinSid = CAST(CAST(SUBSTRING(@xStrSid , 3,1) AS TINYINT) AS VARBINARY)

SET @xBinSid = @xBinSid + 0x05

SET @xBinSid = @xBinSid + CAST(CAST(SUBSTRING(@xStrSid , 5,1) AS TINYINT) AS BINARY(6))

SET @xStrSid = SUBSTRING(@xStrSid,7,LEN(@xStrSid)-6)

DECLARE @oneInt BIGINT

WHILE CHARINDEX('-',@xStrSid) > 0

BEGIN

SET @oneInt = CAST(SUBSTRING(@xStrSid,1,CHARINDEX('-',@xStrSid)-1) AS BIGINT)

SET @xBinSid = @xBinSid + CAST(REVERSE(CAST(@oneInt AS VARBINARY)) AS VARBINARY(4))

SET @xStrSid = SUBSTRING(@xStrSid,CHARINDEX('-',@xStrSid)+1,LEN(@xStrSid))

END

SET @oneInt = CAST(@xStrSid AS BIGINT)

SET @xBinSid = @xBinSid + CAST(REVERSE(CAST(@oneInt AS VARBINARY)) AS VARBINARY(4))

-- select @xBinSid , suser_sname(@xBinSid)

RETURN ( @xBinSid )

END

I use this function for SCOM reports. Somehow the collected user names for security logs are wrong (has to do with different formats of win 2003 and win 2008 event logs), but the SIDs are usable and so I can convert these to user names (without external code).