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