如何检查 SQL Server 中的用户角色

Netwrix Auditor for SQL Server

1. 打开 Netwrix Auditor，然后依次导航到 Reports -> Predefined -> SQL Server - State-in-Time -> SQL Server 中的 Account Permissions。
2. 指定以下筛选条件：
   • 在 User account 筛选条件中，输入完整的用户名（例如 MILKYWAY/TomSimpson）。
   • 在 Object type 筛选条件中，选择 Server Instance, Database。
3. 单击 View 以生成一份清晰的报告，显示该用户的有效权限。

原生解决方案

列出某用户的 SQL Server 角色

1. 启动 Microsoft SQL Server Management Studio (MSSMS)。
2. 在 File 菜单中，单击 Connect Object Explorer。
3. 在 Connect to Server 对话框中，指定以下设置：
   • 在“服务器类型”列表框中，选择“Database Engine”。
   • 在“服务器名称”文本框中，输入 SQL 群集服务器的名称。
   • 在“身份验证”列表框中，选择“SQL Server Authenticationmethod”，并指定要使用的凭据。如果您不想在每次连接到服务器时都重新输入密码，请勾选“Remember password”。
4. 单击“Connect”。
5. 单击“Execute”（或按“F5”键）。
6. 连接成功后，单击 New Query 并将以下脚本粘贴到查询字段中：

      select r.name as Role, m.name as Principal

from

    master.sys.server_role_members rm

    inner join

    master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

    inner join

    master.sys.server_principals m on m.principal_id = rm.member_principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• 查看查询执行结果中的服务器级角色和主体（成员名称）列表：

为用户在 SQL Server 中查询数据库角色

1. 启动 Microsoft SQL Server Management Studio (MSSMS)。
2. 在 文件 菜单上，单击 Connect Object Explorer。
3. 在 Connect to Server 对话框中，指定以下设置：
   • 在 Server type 下拉列表框中，选择Database Engine。
   • 在 Server name 文本框中，输入 SQL 群集服务器的名称。
   • 在 Authentication 下拉列表框中，选择SQL Server Authentication method 并指定要使用的凭据。如果您不想在每次连接到服务器时都重新输入密码，请勾选Remember password。
4. 单击 Connect。
5. 单击 Execute (或按 F5 键)。
6. 连接后，选择需要查询用户角色的 Database 。
7. 单击 New Query 并将以下脚本粘贴到查询框中：

      SELECT r.name role_principal_name, m.name AS member_principal_name

FROM sys.database_role_members rm 

JOIN sys.database_principals r 

    ON rm.role_principal_id = r.principal_id

JOIN sys.database_principals m 

    ON rm.member_principal_id = m.principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• 查看查询执行结果中的服务器级角色和主体（成员名称）列表：
  

了解如何在不运行任何查询的情况下检查 SQL Server 中的用户角色。

Microsoft SQL Server 提供角色，帮助数据库管理员管理对结构化数据的权限。服务器级角色顾名思义会授予对整个服务器的访问权限，类似于 Windows 世界中的组。每个 SQL 数据库也可以拥有各自独特的权限和角色。

为了维持安全性并遵从包括 PCI DSS 和 HIPAA 在内的多项法规，你需要了解每个用户被分配了哪些服务器级和数据库级角色。由于涉及的复杂性，如果你只能使用原生工具，那么这项工作会非常棘手。

首先，服务器角色、权限、用户凭据和依赖项等服务器级设置都存储在 master 数据库中。使用 sys.server_principals 系统视图。,

• 要在 SQL Server 中列出用户和服务器角色，你可以查询诸如 sys.server_principals 之类的系统视图。
• 要列出 SQL Server 中数据库的用户和角色，你可以查询诸如 sys.database_principals 之类的系统视图。

虽然存储过程可以帮助你管理服务器的部分区域，但你仍需要使用查询来生成自定义报告（例如，通过特定列名匹配多个表的报告）。例如，服务器级别的角色成员信息存储在 master 数据库的 server_role_members 系统视图中。由于主体（principals）的 ID 已关联，你可以通过基于 ID 编号将 sys.server_principals 与 master.sys.server_role_members 进行连接的查询，来获取 SQL Server 用户角色的摘要。尽管用户可以查看自己的服务器角色成员信息以及固定服务器角色（fixed server roles）中每个成员的主体 ID，但请记住，查看所有服务器角色成员需要额外权限，或需要成为 security admin 固定服务器角色的成员。

要收集数据库级别的信息，你必须在每个数据库中分别查询 SQL Server 的数据库角色，这可能会耗费大量时间。此外，角色还可以嵌套：数据库用户、应用程序角色以及其他数据库角色都可以成为某个数据库角色的成员。

简而言之，使用原生工具获取有关当前用户角色的完整信息可能会非常复杂，甚至让人感到疲惫不堪。另一方面，使用 Netwrix Auditor，你可以在几次点击内以易读的格式获取谁持有哪些服务器和数据库角色的详细信息。产品中包含的关联报告使你的专家能够快速梳理整个服务器范围内嵌套用户角色成员关系的复杂情况，从而简化调查流程。你将获得所需的全部关键细节：用户有权访问的每个对象的列表（包含其路径和对象类型）、已授予的权限、这些权限是如何授予的（例如直接授予、通过角色成员关系授予等），以及它们是显式的还是继承的。你可以从不同角度分析权限，包括账户级别、对象级别和服务器级别。所有这些信息都会以清晰呈现并可直接获取的方式提供，无需编写脚本、导出到 Excel，也无需手动逐项排查。