convert an SQL Server login SID to a readable string

ConvertSQLServerSIDToReadableString.sql

/*

Ref: http://www.codykonior.com/2015/05/31/how-to-convert-an-sql-login-sid-to-a-readable-string/

Note that if you're doing this on the output from an Audit Login trace (http://www.safesql.com/2015/06/03/sql-server-security-and-renamed-logins/):

Remove the first Case When
Len needs to be replaced with Datalength
sp.sid replaced with al.LoginSid


*/



Select  *
From    sys.server_principals sp
Outer Apply (
    Select  Case
            When    sp.type_desc In ('WINDOWS_LOGIN',  'WINDOWS_GROUP') 
            Then    'S-' +
                    Convert(Varchar, Convert(Int, Substring(sp.sid, 1, 1))) +
                    '-' +
                    Convert(Varchar, Convert(Int, Substring(sp.sid, 3, 6))) +
                    '-' +
                    Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.sid, 9, 4))))) +
                    Case 
                    When    Len(sp.sid) > 13 
                    Then    '-' + 
                            Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.sid, 13, 4)))))
                    Else    '' 
                    End +
                    Case 
                    When    Len(sp.sid) > 17 
                    Then    '-' + 
                            Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.sid, 17, 4)))))
                    Else    '' 
                    End +
                    Case 
                    When    Len(sp.sid) > 21 
                    Then    '-' +
                            Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.sid, 21, 4)))))
                    Else    '' 
                    End +
                    Case 
                    When    Len(sp.sid) > 25 
                    Then    '-' +
                            Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.sid, 25, 4)))))
                    Else    '' 
                    End +
                    Case    
                    When    Len(sp.sid) > 29 
                    Then    '-' + 
                            Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.sid, 29, 4)))))
                    Else    '' 
                    End 
            End As sid_string
    ) sp2