Início / dba / Perguntas / 175271
Accepted
Heinzi
Asked: 2017-06-03 04:57:36 +0800 CST

Como faço para traduzir um SID do Windows para um server_user_sid do SQL Server?

  • 772

Existe essa função do SQL Server SUSER_SNAMEque traduz um server_user_sid para um nome de usuário. Isso é útil para traduzir SIDs do Windows conhecidos para nomes de usuário (potencialmente localizados).

Exemplo:

SELECT SUSER_SNAME(0x01020000000000052000000021020000)

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

Com alguma pesquisa no Google e tentativa e erro (= criar o usuário manualmente e verificar sys.server_principalsdepois), determinei as seguintes equivalências:

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

Qual é o algoritmo para converter SIDs do Windows em server_user_sids do SQL Server?

sql-server windows

2 respostas

  1. Best Answer

    SIDs na forma de 0x01020000000000052000000021020000não são SIDs "SQL Server". Isso é simplesmente o valor binário subjacente do SID. Outra forma que pode assumir (e ainda ser o mesmo valor) é a forma "string" ( SID String Format Syntax ), que se parece com S-1-5-32-545(referido como sendo o formato "SDDL" em alguma documentação do MSDN, embora o SDDL cubra mais do que apenas SID). Ambos são o mesmo SID do Windows. Essa configuração é semelhante à forma como os GUIDs têm uma representação de cadeia de caracteres diferente do valor binário subjacente.

    Existe uma função interna não documentada, SID_BINARY, que faz essa tradução do formato SDDL para o formato binário:

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

    Essa função deve funcionar na maioria dos tipos de SID. As duas consultas a seguir mostram que está funcionando corretamente para certificados e chaves assimétricas (você pode verificar a tradução adequada, pois essas duas exibições de catálogo do sistema têm ambas as formas do SID). E funcionaria para quaisquer logins criados a partir de certificados e chaves assimétricas, pois os SIDs para esses (tanto logins quanto usuários) são os SIDs de certificados/chaves:

    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;

    Observe que os principais dos tipos "S" (Login do SQL Server/Usuário do SQL Server) e "R" (Função de servidor/Função de banco de dados) não têm uma representação SDDL, pois não são SIDs do Windows. Esses dois tipos de principais têm SIDs proprietários do SQL Server, então acho que seriam "SIDs do SQL Server", embora a distinção (entre SIDs do Windows e SIDs do SQL Server) seja de valor e não de forma.

    Se você não quiser usar uma função não documentada, isso também pode ser feito via SQLCLR usando a classe SecurityIdentifier do .NET .

    Funções SQLCLR pré-criadas para fazer essas traduções podem ser encontradas na versão Free da biblioteca SQL# (que eu criei): Convert_SddlSidToBinary (faz a mesma tradução que SID_BINARY) e Convert_BinarySidToSddl .

    • 14

  2. sys.server_principalsé seu amigo, pois expõe a versão do Windows do SID.

    Consulte a solução de Aaron: Mapear entre os SIDs do SQL Server e os SIDs do Windows

    Para completar, segue o código:

    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;
    • 2

relate perguntas