SQL Server 中的服务器和数据库角色Server and Database Roles in SQL Server

所有版本的 SQL Server 均使用基于角色的安全,它允许您为角色、用户组而不是各个用户分配权限。All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. 固定服务器和固定数据库角色具有分配给它们的一组固定的权限。Fixed server and fixed database roles have a fixed set of permissions assigned to them.

固定服务器角色Fixed Server Roles

固定服务器角色具有一组固定的权限,并且适用于整个服务器范围。Fixed server roles have a fixed set of permissions and server-wide scope. 它们专门用于管理 SQL Server,且不能更改分配给它们的权限。They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed. 可以在数据库中不存在用户帐户的情况下向固定服务器角色分配登录。Logins can be assigned to fixed server roles without having a user account in a database.

重要

sysadmin 固定服务器角色包含所有其他角色并且具有无限范围。The sysadmin fixed server role encompasses all other roles and has unlimited scope. 请不要将这些主体添加到此角色,除非它们是高度信任的。Do not add principals to this role unless they are highly trusted. sysadmin 角色成员对所有服务器数据库和资源有不可撤消的管理特权。sysadmin role members have irrevocable administrative privileges on all server databases and resources.

将用户添加到固定服务器角色时,请仔细选择。Be selective when you add users to fixed server roles. 例如,bulkadmin 角色允许用户将任意本地文件的内容插入表中,这样可能会损坏数据的完整性。For example, the bulkadmin role allows users to insert the contents of any local file into a table, which could jeopardize data integrity. 有关固定服务器角色和权限的完整列表,请参阅 SQL Server 联机丛书。See SQL Server Books Online for the complete list of fixed server roles and permissions.

固定数据库角色Fixed Database Roles

固定数据库角色具有一组预定义的权限,这些权限旨在允许您轻松管理权限组。Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. db_owner 角色的成员可对数据库执行所有配置和维护活动。Members of the db_owner role can perform all configuration and maintenance activities on the database.

有关 SQL Server 预定义角色的更多信息,请参阅以下资源。For more information about SQL Server predefined roles, see the following resources.

资源Resource 说明Description
服务器级角色Server-Level Roles 描述固定服务器角色以及与 SQL Server 相关联的权限。Describes fixed server roles and the permissions associated with them in SQL Server.
数据库级别的角色Database-Level Roles 描述固定数据库角色及与其关联的权限Describes fixed database roles and the permissions associated with them

数据库角色和用户Database Roles and Users

要使用数据库对象,必须将登录映射到数据库用户帐户。Logins must be mapped to database user accounts in order to work with database objects. 这样就可以将数据库用户添加到数据库角色,从而继承与这些角色关联的任何权限集。Database users can then be added to database roles, inheriting any permission sets associated with those roles. 可以授予所有权限。All permissions can be granted.

当为应用程序设计安全性时,还必须考虑 public 角色、dbo 用户帐户和 guest 帐户。You must also consider the public role, the dbo user account, and the guest account when you design security for your application.

公共角色The public Role

public 角色包含在每个数据库中,包括系统数据库。The public role is contained in every database, which includes system databases. 无法删除该角色,也无法向其中添加用户或从中删除用户。It cannot be dropped and you cannot add or remove users from it. 授予 public 角色的权限由所有其他用户和角色继承,因为默认情况下,它们属于 public 角色。Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. 仅为 public 角色授予您希望所有用户都具有的权限。Grant public only the permissions you want all users to have.

dbo 用户帐户The dbo User Account

dbo 或数据库所有者是具有在数据库中执行所有活动的默示权限的用户帐户。The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. sysadmin 固定服务器角色的成员会自动映射到 dboMembers of the sysadmin fixed server role are automatically mapped to dbo.

备注

dbo也是架构的名称,如SQL Server 中的所有权和用户架构分离中所述。dbo is also the name of a schema, as discussed in Ownership and User-Schema Separation in SQL Server.

dbo 用户帐户经常与 db_owner 固定数据库角色相混淆。The dbo user account is frequently confused with the db_owner fixed database role. db_owner 的作用域是一个数据库;sysadmin 的作用域是整个服务器。The scope of db_owner is a database; the scope of sysadmin is the whole server. db_owner 角色中的成员无法授予 dbo 用户特权。Membership in the db_owner role does not confer dbo user privileges.

guest 用户帐户The guest User Account

用户经过身份验证并允许登录 SQL Server 的实例后,用户需要访问的每个数据库中必须存在一个单独的用户帐户。After a user has been authenticated and allowed to log in to an instance of SQL Server, a separate user account must exist in each database the user has to access. 要求每个数据库中具有用户帐户会阻止用户连接到 SQL Server 的实例,并且会阻止用户访问服务器上的所有数据库。Requiring a user account in each database prevents users from connecting to an instance of SQL Server and accessing all the databases on a server. 通过允许没有数据库用户帐户的登录访问数据库,可在数据库中包含 guest 用户帐户时避开此需求。The existence of a guest user account in the database circumvents this requirement by allowing a login without a database user account to access a database.

在所有版本的 SQL Server 中,guest 帐户均为内置帐户。The guest account is a built-in account in all versions of SQL Server. 默认情况下,它在新的数据库中是禁用的。By default, it is disabled in new databases. 如果启用此帐户,则可以通过撤消其 CONNECT 权限(方法是执行 Transact-SQL REVOKE CONNECT FROM GUEST 语句)来禁用此帐户。If it is enabled, you can disable it by revoking its CONNECT permission by executing the Transact-SQL REVOKE CONNECT FROM GUEST statement.

重要

避免使用 guest 帐户;没有其自己的数据库权限的所有登录都会获取授予此帐户的数据库权限。Avoid using the guest account; all logins without their own database permissions obtain the database permissions granted to this account. 如果必须使用 guest 帐户,请为其授予最小权限。If you must use the guest account, grant it minimum permissions.

有关 SQL Server 登录名、用户和角色的更多信息,请参阅以下资源。For more information about SQL Server logins, users and roles, see the following resources.

资源Resource 说明Description
数据库引擎权限入门Getting Started with Database Engine Permissions 包含指向描述主体、角色、凭据、安全对象和权限的主题的链接。Contains links to topics that describe principals, roles, credentials, securables and permissions.
主体Principals 描述主体并包含指向描述服务器和数据库角色的主题的链接。Describes principals and contains links to topics that describe server and database roles.

另请参阅See also