确定有效的数据库引擎权限

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics分析平台系统 （PDW）Microsoft Fabric 预览版中的 SQL 数据库

本文介绍如何确定对 SQL Server 数据库引擎中的各种对象拥有权限的人员。 SQL Server 会实现数据库引擎的两个权限系统。 固定角色的旧系统拥有预先配置的权限。 从 SQL Server 2005 (9.x) 开始，提供了更灵活、更精确的系统。

应当牢记以下事项：

• 有效权限是这两个权限系统的聚合。
• 拒绝权限将替代授予权限。
• 如果某个用户是 sysadmin 固定服务器角色的成员，则不会进一步检查权限，因此不会强制执行拒绝。
• 旧系统和新系统具有相似之处。 例如，sysadmin 固定服务器角色中的成员身份就与拥有 CONTROL SERVER 权限相似。 但是，这两个系统并不完全相同。 例如，如果某个登录名仅拥有 CONTROL SERVER 权限，而某个存储过程检查 sysadmin 固定服务器角色中的成员身份，则权限检查将会失败。 反之亦然。
• 在 Fabric SQL 数据库中，数据库用户的 Microsoft Entra ID 是唯一受支持的身份验证方法。 服务器级角色和权限不可用，仅数据库级别。 有关详细信息，请参阅 Microsoft Fabric 中 SQL 数据库的授权。

Summary

• 服务器级权限可以来自固定服务器角色或用户定义的服务器角色中的成员身份。 每个人都属于 public 固定服务器角色，获得其中分配的任何权限。
• 服务器级权限可以来自授予登录名或用户定义的服务器角色的权限。
• 数据库级权限可以来自固定数据库角色中的成员身份，也可以来自每个数据库中的用户定义的数据库角色中的成员身份。 每个人都属于 public 固定数据库角色，获得其中分配的任何权限。
• 数据库级权限可以来自授予每个数据库中的用户或用户定义的数据库角色的权限。
• 可从 guest 登录名或 guest 数据库用户（如果已启用）获得权限。 默认情况下，guest 登录名和用户会被禁用。
• Windows 用户可以是 Windows 组中能够拥有登录名的成员。 当 Windows 用户将 Windows 令牌与 Windows 组的安全标识符连接并提供该令牌时，SQL Server 将会了解 Windows 组的成员身份。 由于 SQL Server 不会管理或接收有关 Windows 组成员身份的自动更新，因此，SQL Server 无法可靠地报告从 Windows 组成员身份接收的 Windows 用户的权限。
• 通过切换到应用程序角色并提供密码，可以获得权限。
• 通过执行包含 EXECUTE AS 子句的存储过程，可以获得权限。
• 拥有 IMPERSONATE 权限的登录名或用户可以获取权限。
• 本地计算机管理员组的成员始终可将其特权提升到 sysadmin。 （不适用于 SQL 数据库。）
• securityadmin 固定服务器角色的成员可以提升其许多特权，在某些情况下可以将特权提升到 sysadmin。 （不适用于 SQL 数据库。）
• SQL Server 管理员可以查看有关所有登录名和用户的信息。 特权较低的用户通常只能查看有关其自身标识的信息。

旧的固定角色权限系统

固定服务器角色和固定数据库角色拥有不可更改的预先配置的权限。 若要确定谁是固定服务器角色的成员，请执行以下查询：

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

若要确定固定数据库角色的成员，请在每个数据库中执行以下查询。

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

若要了解授予每个角色的权限，请参阅联机丛书（服务器级角色和数据库级角色）中演示的角色说明。

新的粒度权限系统

此系统具有弹性，这意味着，如果将它设置得很精确，它可能会变得很复杂。 为了简化操作，它可帮助创建角色，将权限分配给角色，然后将用户组添加到角色。 如果数据库开发团队按架构划分活动，然后向整个架构而不是单个表或过程授予角色权限，则操作就会更轻松。 实际应用场景很复杂，并且业务需求可能会产生意外的安全要求。

下图显示了权限以及它们彼此之间的关系。 一些较高级别的权限（如 CONTROL SERVER）被多次列出。 在本文中，图表太小，无法阅读。 你可以下载 PDF 格式的完整数据库引擎权限文章。

Security classes

可在服务器级别、数据库级别、架构级别或对象等等级别授予权限。共有 26 个级别（称为类）。 类按字母顺序的完整列表是：APPLICATION ROLE、ASSEMBLY、ASYMMETRIC KEY、AVAILABILITY GROUP、CERTIFICATE、CONTRACT、DATABASE、DATABASESCOPED CREDENTIAL、ENDPOINT、FULLTEXT CATALOG、FULLTEXT STOPLIST、LOGIN、MESSAGE TYPE、OBJECT、REMOTE SERVICE BINDING、ROLE、ROUTE、SCHEMA、SEARCH PROPERTY LIST、SERVER、SERVER ROLE、SERVICE、SYMMETRIC KEY、TYPE、USER、XML SCHEMA COLLECTION。 （某些类在某些类型的 SQL Server 中不可用。）若要提供有关每个类的完整信息，需要执行不同的查询。

Principals

权限将授予“主体”。 主体可以是服务器角色、登录名、数据库角色或用户。 登录名可以表示包含许多 Windows 用户的 Windows 组。 由于 Windows 组不由 SQL Server 维护，因此，SQL Server 不一定总知道谁是 Windows 组的成员。 当 Windows 用户连接到 SQL Server 时，登录数据包将包含该用户的 Windows 组成员身份令牌。

当 Windows 用户根据 Windows 组使用登录名进行连接时，某些活动可能需要 SQL Server 创建登录名或用户来表示单个 Windows 用户。 例如，某个 Windows 组（“工程师”）包含用户（Mary、Todd、Pat），并且“工程师”组包含一个数据库用户帐户。 如果 Mary 拥有权限并创建一个表，则可以创建用户 (Mary) 作为该表的所有者。 或者，如果拒绝 Todd 拥有“工程师”组中的其他成员拥有的权限，则必须创建用户 Todd 来跟踪权限拒绝。

请记住，一个 Windows 用户可以是多个 Windows 组（例如“工程师”和“经理”）的成员。 针对“工程师”登录名、“经理”登录名、单个用户、用户所属角色授予或拒绝的权限都将被聚合，并评估权限有效性。 HAS_PERMS_BY_NAME 函数可以显示某个用户或登录名是否拥有特定权限。 但是，没有确定权限授予或拒绝源的确切方法。 研究权限列表，也许还要使用试错方法进行试验。

Useful queries

Server permissions

以下查询会返回在服务器级别授予或拒绝的权限列表。 只能在 master 数据库中执行此查询。

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Database permissions

以下查询会返回在数据库级别授予或拒绝的权限列表。 应在每个数据库中执行此查询。

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

可以将权限表中的每个权限类联接到其他提供有关该安全对象类的相关信息的系统视图。 例如，以下查询将提供受权限影响的数据库对象的名称。

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

使用 HAS_PERMS_BY_NAME 函数确定特定用户（在本例中为 TestUser）是否拥有权限。 For example:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

有关语法的详细信息，请参阅 HAS_PERMS_BY_NAME。

Next steps

• 数据库引擎权限入门
• 教程：数据库引擎入门