Each SQL Server login has a SID associated but stored in Varbinary instead of the readable Microsoft string format; the latter being important if you need to compare SQL Server server principals against SIDs from Active Directory.

For a while I was using a function to convert them but I came up with a "better way of doing it in a deterministic manner."

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

Minified version:

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

Note that if you're doing this on the output from an Audit Login trace:

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