数据库引擎权限入门

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

数据库引擎中的权限通过登录名和服务器角色在服务器级别进行管理，以及通过数据库用户和数据库角色在数据库级别进行管理。 SQL 数据库的模型在每个数据库中公开同一系统，但服务器级别权限不可用。 本文章复习一些基本的安全概念，然后介绍典型的权限实现。

注意

Microsoft Entra ID 以前称为 Azure Active Directory (Azure AD)。

安全主体

安全主体是使用 SQL Server 并可以为其分配执行操作的权限的标识的正式名称。 它们通常是人员或人员组，但可以是伪装成人员的其他实体。 安全主体可以使用列出的 Transact-SQL 或通过使用 SQL Server Management Studio 来进行创建和管理。

登录

登录名是用于登录 SQL Server 数据库引擎的单个用户帐户。 SQL Server 和 SQL 数据库支持基于 Windows 身份验证的登录名和基于 SQL Server 身份验证的登录名。 有关这两种类型的登录名的信息，请参阅 Choose an Authentication Mode。

固定服务器角色

在 SQL Server 中，固定服务器角色是一组预配置的角色，便于对服务器级别权限进行分组。 可以使用 ALTER SERVER ROLE ... ADD MEMBER 语句将登录名添加到角色。 有关详细信息，请参阅 ALTER SERVER ROLE (Transact-SQL))。 SQL 数据库不支持固定服务器角色，但在 master 数据库中有两个角色（dbmanager 和 loginmanager）充当服务器角色。

用户定义的服务器角色

在 SQL Server 中，可以创建你自己的服务器角色并向它们分配服务器级权限。 可以使用 ALTER SERVER ROLE ... ADD MEMBER 语句将登录名添加到服务器角色。 有关详细信息，请参阅 ALTER SERVER ROLE (Transact-SQL))。 SQL 数据库不支持用户定义的服务器角色。

数据库用户

通过在数据库中创建数据库用户并将该数据库用户映射到登录名来授予登录名对数据库的访问权限。 通常，数据库用户名与登录名相同，尽管它不必要相同。 每个数据库用户均映射到单个登录名。 一个登录名只能映射到数据库中的一个用户，但可以映射为多个不同数据库中的数据库用户。

也可以创建不具有相应登录名的数据库用户。 这些数据库用户称为“包含的数据库用户”。 Microsoft 鼓励使用包含的数据库用户，因为这样可以更轻松地将你的数据库移到另一个服务器。 与登录名类似，包含的数据库用户可以使用 Windows 身份验证或 SQL Server 身份验证。 有关详细信息，请参阅 包含的数据库用户 - 使你的数据库可移植。

有 12 种类型的用户，它们在如何进行身份验证以及所表示的人员方面略有差异。 若要查看用户列表，请参阅 CREATE USER (Transact-SQL)。

固定数据库角色

固定数据库角色是一组预配置的提供方便的数据库级权限组的角色。 可以使用 ALTER ROLE ... ADD MEMBER 语句将数据库用户和用户定义的数据库角色添加到固定数据库角色。 有关详细信息，请参阅 ALTER ROLE (Transact-SQL)。

用户定义的数据库角色

具有 CREATE ROLE 权限的用户可以创建新的用户定义的数据库角色来表示具有常用权限的用户组。 通常对整个角色授予或拒绝权限，从而简化了权限管理和监视。 可以使用 ALTER ROLE ... ADD MEMBER 语句向数据库角色添加数据库用户。 有关详细信息，请参阅 ALTER ROLE (Transact-SQL)。

其他主体

这里不讨论的其他安全主体包括基于证书或非对称密钥的应用程序角色、登录名和用户。

有关显示 Windows 用户、Windows 组、登录名和数据库用户之间关系的图形，请参阅 Create a Database User。

典型的方案

下面的示例表示配置权限的常见和建议的方法。

在 Windows Active Directory 或 Microsoft Entra ID 中

1. 为每个人员创建一个用户。

2. 创建表示工作单位和工作职能的 Windows 组。

3. 将 Windows 用户添加到 Windows 组。

如果连接的人员将连接到多个数据库

1. 为 Windows 组创建登录名。 （如果使用 SQL Server 身份验证，请跳过 Active Directory 步骤，并在此处创建 SQL Server 身份验证登录名。）

2. 在用户数据库中，为表示 Windows 组的登录名创建一个数据库用户。

3. 在用户数据库中，创建一个或多个用户定义的数据库角色，每个角色表示相似的职能。 例如，财务分析人员和销售分析人员。

4. 将数据库用户添加到一个或多个用户定义的数据库角色。

5. 向用户定义的数据库角色授予权限。

如果连接的人员将只连接到一个数据库

1. 在用户数据库中，为 Windows 组创建一个包含的数据库用户。 （如果使用 SQL Server 身份验证，请跳过 Active Directory 步骤，并在此处创建包含的数据库用户 SQL Server 身份验证。）

2. 在用户数据库中，创建一个或多个用户定义的数据库角色，每个角色表示相似的职能。 例如，财务分析人员和销售分析人员。

3. 将数据库用户添加到一个或多个用户定义的数据库角色。

4. 向用户定义的数据库角色授予权限。

此时的典型结果是，Windows 用户是 Windows 组的成员。 Windows 组在 SQL Server 或 SQL 数据库中具有登录名。 该登录名将映射到用户数据库中的用户标识。 用户是数据库角色的成员。 现在，你需要将权限添加到角色。

分配权限

大多数权限语句具有以下格式：

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

• AUTHORIZATION 必须为 GRANT、 REVOKE 或 DENY。

• PERMISSION 确立允许或禁止哪个操作。 SQL Server 和 SQL 数据库的确切权限数量不同。 这些权限在《权限（数据库引擎）》文章和下面引用的图表中列出。

• ON SECURABLE::NAME 是安全对象（服务器、服务器对象、数据库或数据库对象）的类型及其名称。 某些权限不需要 ON SECURABLE::NAME，因为它是明确的或在上下文中不适当。 例如，CREATE TABLE 权限不需要 ON SECURABLE::NAME 子句（GRANT CREATE TABLE TO Mary; 允许 Mary 创建表）。

• PRINCIPAL 是获得或失去权限的安全主体（登录名、用户或角色）。 尽可能向角色授予权限。

下面的示例 grant 语句将对 UPDATE 架构中包含的 Parts 表或视图的 Production 权限授予名为 PartsTeam的角色：

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

下面的示例 grant 语句将授予对 Production 架构的 UPDATE 权限，并通过扩展该权限将此架构中包含的任何表或视图的权限授予名为 ProductionTeam 的角色，相较于在各个对象级别分配权限，这是一种更有效且更受欢迎的方法：

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

使用 GRANT 语句向安全主体（登录名、用户和角色）授予权限。 使用 DENY 命令显式拒绝权限。 使用 REVOKE 语句删除以前授予或拒绝的权限。 权限是累积的，用户将获得授予该用户、登录名和任何组成员身份的所有权限；但是任何权限拒绝将覆盖所有授予。

提示

一个常见错误是尝试使用 GRANT 而不是 DENY 来删除 REVOKE。 当用户从多个源获得权限时，这会导致问题；此错误相当常见。 以下示例演示了主体。

Sales 组通过语句 SELECT 获得对 OrderStatus 表的 GRANT SELECT ON OBJECT::OrderStatus TO Sales;权限。 用户 Jae 是 Sales 角色的成员。 还通过语句 SELECT 在 Jae 自己的用户名下授予 Jae 对 OrderStatus 表的 GRANT SELECT ON OBJECT::OrderStatus TO Jae;权限。 假设管理员希望删除对 Sales 角色的 GRANT 。

• 如果管理员正确执行 REVOKE SELECT ON OBJECT::OrderStatus TO Sales;，则 Jae 将通过其个人 SELECT 语句保留对 OrderStatus 表的 GRANT 访问权限。

• 如果管理员未正确执行 DENY SELECT ON OBJECT::OrderStatus TO Sales;，则 Jae 作为 Sales 角色的成员将被拒绝 SELECT 权限，因为对 Sales 的 DENY 将覆盖其个人 GRANT。

注意

可以使用 Management Studio 配置权限。 在对象资源管理器中查找安全对象，右键单击该安全对象，然后选择“属性”。 选择“权限”页 。 有关使用权限页的帮助，请参阅 Permissions or Securables Page。

权限层次结构

权限具有父/子层次结构。 也就是说，如果你授予对数据库的 SELECT 权限，则该权限包括对数据库中所有（子）架构的 SELECT 权限。 如果你授予对架构的 SELECT 权限，则该权限包括对架构中所有（子）表和视图的 SELECT 权限。 权限是可传递的；也就是说，如果你授予对数据库的 SELECT 权限，则该权限包括对所有（子级）架构和所有（孙级）表和视图的 SELECT 权限。

权限还可以涵盖权限。 对某个对象的 CONTROL 权限通常为你提供对该对象的所有其他权限。

由于父/子层次结构和包含的层次结构可以作用于相同的权限，因此权限系统可以变得很复杂。 例如，让我们以数据库 (SalesDB) 的架构 (Customers) 中的表 (Region) 为例。

• CONTROL 权限包括对表 Region 的所有其他权限，包括 ALTER、 SELECT、 INSERT、 UPDATE、 DELETE、 and some other permissions.

• SELECT 包括对 Region 表的 SELECT 权限。

因此，对 Region 表的 SELECT 权限可以通过以下六个语句中的任一个实现：

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

授予最少权限

上面列出的第一个权限 (GRANT SELECT ON OBJECT::Region TO Jae;) 的粒度最细，这就是说，该语句是授予 SELECT的可能的最少权限。 没有向它附带的从属对象授予权限。 始终授予尽可能少的权限（可阅读《最低特权原则》了解更多信息）是一种很好的原则，但同时（与之矛盾）尝试在更高级别上授权可简化授权系统。 因此，如果 Jae 需要对整个架构的权限，则只需在架构级别授予 SELECT 一次，而不是在表或视图级别授予 SELECT 多次。 数据库的设计可以极大地影响此策略的成功程度。 当数据库设计为需要相同权限的对象都包含在单个架构中时，此策略最有效。

提示

从头设计数据库及其对象时，规划哪些用户或哪些应用程序将访问哪些对象，并基于此尽可能多地根据大量访问类型在架构中放置对象，即表、视图、函数和存储过程。

权限关系图

下图显示了权限以及它们彼此之间的关系。 多次列出了某些更高级别的权限（如 CONTROL SERVER）。 在本文中，海报太小了，因此无法查看。 可以 pdf 格式下载完整版《数据库引擎权限文章》。

有关显示数据库引擎主体与服务器和数据库对象之间关系的图形，请参阅权限层次结构（数据库引擎）。

权限与固定服务器和固定数据库角色

固定服务器角色和固定数据库角色的权限很相似，但与细粒度的权限并不完全相同。 例如，sysadmin 固定服务器角色的成员在 SQL Server 实例上具有所有权限，如同使用 CONTROL SERVER 权限登录一样。 但是授予 CONTROL SERVER 权限不会使登录名成为 sysadmin 固定服务器角色的成员，将登录名添加到 sysadmin 固定服务器角色不会显式授予登录名 CONTROL SERVER 权限。 有时存储过程将通过检查固定角色而不检查细粒度的权限来检查权限。 例如，分离数据库需要具有 db_owner 固定数据库角色中的成员身份。 等效的 CONTROL DATABASE 权限并不够。 这两个系统并行运行，但彼此很少进行交互。 Microsoft 建议尽可能使用更新的细粒度权限系统，而不是使用固定角色。

监视权限

以下视图返回安全信息。

• 可以使用 sys.server_principals 视图查看服务器上的登录名和用户定义的服务器角色。 此视图在 SQL 数据库中不可用。

• 可以使用 sys.database_principals 视图查看数据库中的用户和用户定义的角色。

• 可以使用 sys.server_permissions 视图查看授予登录名和用户定义的固定服务器角色的权限。 此视图在 SQL 数据库中不可用。

• 可以使用 sys.database_permissions 视图查看授予用户和用户定义的固定数据库角色的权限。

• 可以使用 sys.database_role_members 视图查看数据库角色成员身份。

• 可以使用 sys.server_role_members 视图查看服务器角色成员身份。 此视图在 SQL 数据库中不可用。

• 有关更多与安全性相关的视图，请参阅安全性目录视图 (Transact-SQL)来进行创建和管理。

示例

以下语句返回有关权限的有用信息。

A. 每个用户的数据库权限的列表

若要返回在数据库（SQL Server 和 SQL 数据库）中授予或拒绝的显式权限，请在数据库中执行以下语句。

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. 列出服务器角色成员

若要返回服务器角色的成员（仅限 SQL Server），请执行以下语句。

SELECT roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName,
    server_role_members.member_principal_id AS MemberPrincipalID,
    members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
LEFT JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

°C 列出作为数据库级别角色成员的所有数据库主体

若要返回数据库角色的成员（SQL Server 和 SQL 数据库），请在数据库中执行以下语句。

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;

另请参阅

• SQL Server 数据库引擎和 Azure SQL Database 的安全中心
• 安全函数 (Transact-SQL)
• 与安全性相关的动态管理视图和函数 (Transact-SQL)
• 安全性目录视图 (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• 确定有效的数据库引擎权限

后续步骤

• 教程：数据库引擎入门
• 创建数据库（教程）
• 教程：SQL Server Management Studio
• 教程：编写 Transact-SQL 语句