How do I translate a Windows SID to an SQL Server server_user_sid?

SIDs in the form of 0x01020000000000052000000021020000 are not "SQL Server" SIDs. That is simply the underlying binary value of the SID. Another form it can take (and still be the same value) is the "string" form (SID String Format Syntax), which looks like S-1-5-32-545 (referred to as being the "SDDL" format in some MSDN documentation, though SDDL covers more than just SIDs). Both are the same Windows SID. This setup is similar to how GUIDs have a string representation that is different than their underlying binary value.

There is an undocumented built-in function, SID_BINARY, that does this translation from the SDDL form into the binary form:

SELECT SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513');
-- 0x01050000000000051500000027035A185996571BAD3724B801020000

This function should work across most SID types. The following two queries show it working correctly for Certificates and Asymmetric Keys (you can verify proper translation since these two system catalog views have both forms of the SID in them). And it would work for any Logins created from Certificates and Asymmetric Keys as the SIDs for those (both Logins and Users) are the Cert / Key SIDs:

SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM   [master].sys.certificates;

SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM   [master].sys.asymmetric_keys;

Please note that principals of types "S" (SQL Server Login / SQL Server User) and "R" (Server Role / Database Role) do not have an SDDL representation as they are not Windows SIDs. These two types of principals have SQL Server properietary SIDs, so I guess these would be "SQL Server SIDs", though the distinction (between Windows SIDs and SQL Server SIDs) is of value and not form.

If you don't want to use an undocumented function, this can also be accomplished via SQLCLR using .NET's SecurityIdentifier class.

Pre-made SQLCLR functions to do these translations can be found in the Free version of the SQL# library (that I created): Convert_SddlSidToBinary (does the same translation as SID_BINARY) and Convert_BinarySidToSddl.

sys.server_principals is your friend since it exposes the Windows version of the SID.

Refer to Aaron's solution : Map between SQL Server SIDs and Windows SIDs

For completeness, below is the code :

CREATE TABLE dbo.TinyNumbers(Number TINYINT PRIMARY KEY);

INSERT dbo.TinyNumbers(Number) 
  SELECT TOP (256) ROW_NUMBER() OVER (ORDER BY number)-1 
  FROM master.dbo.spt_values;

CREATE FUNCTION dbo.GetWindowsSID
(
  @sid VARBINARY(85)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT ADsid = STUFF((SELECT '-' + part FROM 
    (
      SELECT Number = -1, part = 'S-' 
        + CONVERT(VARCHAR(30),CONVERT(TINYINT,CONVERT(VARBINARY(30),LEFT(@sid,1)))) 
        + '-' 
        + CONVERT(VARCHAR(30),CONVERT(INT,CONVERT(VARBINARY(30),SUBSTRING(@sid,3,6))))
      UNION ALL
      SELECT TOP ((LEN(@sid)-5)/4) Number, 
     part = CONVERT(VARCHAR(30),CONVERT(BIGINT,CONVERT(VARBINARY(30), 
  REVERSE(CONVERT(VARBINARY(30),SUBSTRING(@sid,9+Number*4,4)))))) 
      FROM dbo.TinyNumbers ORDER BY Number
    ) AS x ORDER BY Number
    FOR XML PATH(''), TYPE).value(N'.[1]','nvarchar(max)'),1,1,'')
  );
GO

CREATE VIEW dbo.server_principal_sids
AS
  SELECT sp.name, sp.[sid], ad.ADsid, sp.type_desc
    FROM sys.server_principals AS sp
    CROSS APPLY dbo.GetWindowsSID(sp.[sid]) AS ad
    WHERE [type] IN ('U','G') 
    AND LEN([sid]) % 4 = 0;

-- select the data
SELECT name,[sid],ADSid,type_desc FROM dbo.server_principal_sids;