主页 / dba / 问题 / 175271
Accepted
Heinzi
Asked: 2017-06-03 04:57:36 +0800 CST

如何将 Windows SID 转换为 SQL Server server_user_sid？

  • 772

有一个很好的 SQL Server 函数SUSER_SNAME，可以将server_user_sid转换为用户名。这对于将众所周知的 Windows SID 转换为（可能本地化的）用户名很有用。

例子：

SELECT SUSER_SNAME(0x01020000000000052000000021020000)

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

通过一些谷歌搜索和反复试验（=手动创建用户并sys.server_principals随后检查），我确定了以下等价物：

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

将 Windows SID 转换为 SQL Server server_user_sids 的算法是什么？

sql-server windows

2 个回答

  1. Best Answer

    形式的 SID0x01020000000000052000000021020000不是“SQL Server”SID。这只是 SID 的底层二进制值。它可以采用的另一种形式（并且仍然是相同的值）是“字符串”形式（SID 字符串格式语法），它看起来像S-1-5-32-545（在某些 MSDN 文档中称为“SDDL”格式，尽管 SDDL 涵盖的不仅仅是SID）。两者都是相同的 Windows SID。此设置类似于 GUID 如何具有与其基础二进制值不同的字符串表示形式。

    有一个未记录的内置函数 ，SID_BINARY它将 SDDL 形式转换为二进制形式：

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

    此功能应该适用于大多数 SID 类型。以下两个查询显示它对证书和非对称密钥正常工作（您可以验证正确的翻译，因为这两个系统目录视图中都有两种形式的 SID）。它适用于从证书和非对称密钥创建的任何登录，因为这些（登录和用户）的 SID 是证书/密钥 SID：

    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;

    请注意，“S”（SQL Server 登录/SQL Server 用户）和“R”（服务器角色/数据库角色）类型的主体没有 SDDL 表示，因为它们不是 Windows SID。这两种类型的主体具有 SQL Server 专有 SID，所以我猜这些将是“SQL Server SID”，尽管区别（在 Windows SID 和 SQL Server SID 之间）是有价值的而不是形式。

    如果您不想使用未记录的函数，也可以通过 SQLCLR 使用 .NET 的SecurityIdentifier 类来完成。

    可以在SQL#库（我创建的）的免费版本中找到用于执行这些转换的预制 SQLCLR 函数： Convert_SddlSidToBinary（与 进行相同的转换SID_BINARY）和Convert_BinarySidToSddl。

    • 14

  2. sys.server_principals是您的朋友，因为它公开了 Windows 版本的 SID。

    请参阅 Aaron 的解决方案：SQL Server SID 和 Windows SID 之间的映射

    为了完整起见，以下是代码：

    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

相关问题