8

There is this nice SQL Server function SUSER_SNAME which translates a server_user_sid to a user name. This is useful for translating well-known Windows SIDs to (potentially localized) user names.

Example:

SELECT SUSER_SNAME(0x01020000000000052000000021020000)

-- yields 'BUILTIN\USERS' (or, on a German system, 'VORDEFINIERT\Benutzer')

With some Googling and trial-and-error (= create the user manually and check sys.server_principals afterwards) I have determined the following equivalencies:

Built-in User/Group    Windows SID      SQL Server server_user_sid

BUILTIN\USERS          S-1-5-32-545     0x01020000000000052000000021020000
NT AUTHORITY\SYSTEM    S-1-5-18         0x010100000000000512000000

What's the algorithm to convert Windows SIDs to SQL Server server_user_sids?

11

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.

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.