Functions such as SUSER_SID() return the binary represenation of the SID. fn_SIDToString converts the binary representation to the string format S-1-5-21-xxxx-yyyy-zzzz-aaaa.
Usage:
SELECT SUSER_NAME(), SUSER_SID(), dbo.fn_SIDToString(SUSER_SID())
Functions such as SUSER_SID() return the binary represenation of the SID. fn_SIDToString converts the binary representation to the string format S-1-5-21-xxxx-yyyy-zzzz-aaaa.
Usage:
SELECT SUSER_NAME(), SUSER_SID(), dbo.fn_SIDToString(SUSER_SID())
CREATE FUNCTION fn_SIDToString
(
@BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN
IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
DECLARE @StringSID VARCHAR(100)
DECLARE @i AS INT
DECLARE @j AS INT
SELECT @StringSID = 'S-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
SET @j = 9
SET @i = LEN(@BinSID)
WHILE @j < @i
BEGIN
DECLARE @val BINARY(4)
SELECT @val = SUBSTRING(@BinSID, @j, 4)
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
SET @j = @j + 4
END
RETURN ( @StringSID )
END