Coverting text sid to binary in T-SQL

January 14, 2010

If you want to convert a text sid in the form:

S-1-5-21-2127521184-1604012920-1887927527-72713

to something like

010500000000000515000000A065CF7E784B9B5FE77C8770091C0100

in SQL Server, the following code will do it:

CREATE FUNCTION SidToHex (@textsid nvarchar(max))
RETURNS varbinary(256)
AS
BEGIN
	DECLARE @pos int,
	@currentBlock nvarchar(max),
	@blockno nvarchar(max),
	@nextHyphen int,
	@revision int,
	@binarysid varbinary(256),
	@blockval bigint;

	SET @pos = 1
	SET @blockno = 0
	SET @binarysid = 0x

	WHILE @pos < LEN(@textsid)
	BEGIN
		SET @nextHyphen = CHARINDEX('-', @textsid, @pos)
		SET @nextHyphen = CASE @nextHyphen WHEN 0 THEN LEN(@textsid) + 1 ELSE @nextHyphen END
		SET @currentBlock = SUBSTRING(@textsid, @pos, @nextHyphen - @pos)
		SET @blockno = @blockno + 1
		SET @pos = @nextHyphen + 1

		IF @blockno = 1
		BEGIN
			IF @currentBlock <> 'S'
			RETURN -1
		END
		ELSE IF @blockno = 2
			SET @revision = CONVERT(int, @currentBlock)
		ELSE IF @blockno = 3
			SET @binarysid = @binarysid + CAST(CONVERT(int, @currentBlock) as binary(6))
		ELSE
		BEGIN
			SET @blockVal = CONVERT(int, @currentBlock)
			SET @blockVal =
			((@blockVal & 0x000000FF) * 0x1000000) |
			((@blockVal & 0x0000FF00) * 0x100) |
			((@blockVal & 0x00FF0000) / 0x100) |
			((@blockVal & 0xFF000000) / 0x1000000)
			SET @binarysid = @binarysid + CAST(@blockVal as binary(4))
		END
	END

	RETURN CAST(@revision as binary(1)) + CAST(@blockno - 3 as binary(1)) + @binarysid;
END
GO