主体(数据库引擎)Principals (Database Engine)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

“主体” 是可以请求 SQL ServerSQL Server 资源的实体。Principals are entities that can request SQL ServerSQL Server resources. SQL ServerSQL Server 授权模型的其他组件一样,主体也可以按层次结构排列。Like other components of the SQL ServerSQL Server authorization model, principals can be arranged in a hierarchy. 主体的影响范围取决于主体的定义范围:Windows、服务器、数据库;以及主体是不可分割还是集合。The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. 例如,Windows 登录名就是一个不可分主体,而 Windows 组则是一个集合主体。A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. 每个主体都具有一个安全标识符 (SID)。Every principal has a security identifier (SID). 本主题适用于所有版本的 SQL Server,但在 SQL 数据库或 SQL 数据仓库的服务器级别主体上有一些限制。This topic applies to all version of SQL Server, but there are some restictions on server-level principals in SQL Database or SQL Data Warehouse.

SQL Server 级的主体SQL Server-level principals

  • SQL ServerSQL Server 身份验证登录名authentication Login
  • Windows 用户的 Windows 身份验证登录名Windows authentication login for a Windows user
  • Windows 组的 Windows 身份验证登录名Windows authentication login for a Windows group
  • AD 用户的 Azure Active Directory 身份验证登录名Azure Active Directory authentication login for a AD user
  • AD 组的 Azure Active Directory 身份验证登录名Azure Active Directory authentication login for a AD group
  • 服务器角色Server Role

数据库级的主体Database-level principals

  • 数据库用户(有 12 个类型的用户。Database User (There are 12 types of users. 有关详细信息,请参阅 CREATE USER。)For more information, see CREATE USER.)
  • 数据库角色Database Role
  • 应用程序角色Application Role

sa 登录名sa Login

SQL ServerSQL Server sa 登录名是服务器级别主体。The SQL ServerSQL Server sa log in is a server-level principal. 默认情况下,该登录名是在安装实例时创建的。By default, it is created when an instance is installed. SQL Server 2005 (9.x)SQL Server 2005 (9.x)开始,sa 的默认数据库为“master”。Beginning in SQL Server 2005 (9.x)SQL Server 2005 (9.x), the default database of sa is master. 这是对早期版本的 SQL ServerSQL Server的行为的更改。This is a change of behavior from earlier versions of SQL ServerSQL Server. sa 登录名是 sysadmin 固定服务器级别角色的成员。The sa login is a member of the sysadmin fixed server-level role. sa 登录名具有服务器上的所有权限,并且不能受到限制。The sa login has all permissions on the server and cannot be limited. sa 登录名无法删除,但可以禁用,以便任何人都无法使用它。The sa login cannot be dropped, but it can be disabled so that no one can use it.

dbo 用户和 dbo 架构dbo User and dbo Schema

dbo 用户是每个数据库中的特殊用户主体。The dbo user is a special user principal in each database. 所有 SQL Server 管理员、sysadmin 固定服务器角色成员、sa 登录名和数据库所有者,均以 dbo 用户身份进入数据库。All SQL Server administrators, members of the sysadmin fixed server role, sa login, and owners of the database, enter databases as the dbo user. dbo 用户有数据库中的所有权限,并且不能被限制或删除。The dbo user has all permissions in the database and cannot be limited or dropped. dbo 代表数据库所有者,但 dbo 用户帐户与 db_owner 固定数据库角色不同,并且 db_owner 固定数据库角色与作为数据库所有者记录的用户帐户不同。dbo stands for database owner, but the dbouser account is not the same as the db_owner fixed database role, and the db_owner fixed database role is not the same as the user account that is recorded as the owner of the database.
dbo 用户拥有 dbo 架构。The dbo user owns the dbo schema. dbo 架构是所有用户的默认架构,除非指定了其他某个架构。The dbo schema is the default schema for all users, unless some other schema is specified. dbo 架构无法删除。The dbo schema cannot be dropped.

公共服务器角色和数据库角色public Server Role and Database Role

每个登录名都属于 public 固定服务器角色,并且每个数据库用户都属于 public 数据库角色。Every login belongs to the public fixed server role, and every database user belongs to the public database role. 当尚未为某个登录名或用户授予或拒绝为其授予对安全对象的特定权限时,该登录名或用户将继承已授予该安全对象的公共角色的权限。When a login or user has not been granted or denied specific permissions on a securable, the login or user inherits the permissions granted to public on that securable. public 固定服务器角色和 public 固定服务器角色无法删除。The public fixed server role and the public fixed database role cannot be dropped. 但是,可以从 public 角色撤消权限。However you can revoke permissions from the public roles. 默认情况下有许多权限已分配给 public 角色。There are many permissions that are assigned to the public roles by default. 这些权限中的大部分是执行数据库中的日常操作(每个人都应能够执行的操作类型)所需的。Most of these permissions are needed for routine operations in the database; the type of things that everyone should be able to do. 从公共登录名或用户撤消权限时应十分小心,因为这将影响所有登录名/用户。Be careful when revoking permissions from the public login or user, as it will affect all logins/users. 通常不应拒绝公共登录名或用户的权限,因为 Deny 语句会覆盖你可能对个别登录名或用户设定的任何 Grant 语句。Generally you should not deny permissions to public, because the deny statement overrides any grant statements you might make to individuals.

INFORMATION_SCHEMA 和 sys 用户与架构INFORMATION_SCHEMA and sys Users and Schemas

每个数据库都包含两个实体,并且这些实体都作为用户显示在目录视图中:INFORMATION_SCHEMAsysEvery database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. 这些实体供数据库引擎内部使用。These entities are required for internal use by the Database Engine. 它们无法修改或删除。They cannot be modified or dropped.

基于证书的 SQL Server 登录名Certificate-based SQL Server Logins

名称由双井号 (##) 括起来的服务器主体仅供内部系统使用。Server principals with names enclosed by double hash marks (##) are for internal system use only. 下列主体是在安装 SQL ServerSQL Server 时从证书创建的,不应删除。The following principals are created from certificates when SQL ServerSQL Server is installed, and should not be deleted.

  • ##MS_SQLResourceSigningCertificate####MS_SQLResourceSigningCertificate##
  • ##MS_SQLReplicationSigningCertificate####MS_SQLReplicationSigningCertificate##
  • ##MS_SQLAuthenticatorCertificate####MS_SQLAuthenticatorCertificate##
  • ##MS_AgentSigningCertificate####MS_AgentSigningCertificate##
  • ##MS_PolicyEventProcessingLogin####MS_PolicyEventProcessingLogin##
  • ##MS_PolicySigningCertificate####MS_PolicySigningCertificate##
  • ##MS_PolicyTsqlExecutionLogin####MS_PolicyTsqlExecutionLogin##

管理员不可更改这些主体帐户的密码,因为这些密码基于颁发给 Microsoft 的证书。These principal accounts do not have passwords that can be changed by administrators as they are based on certificates issued to Microsoft.

guest 用户The guest User

每个数据库包括一个 guest的行为的更改。Each database includes a guest. 授予 guest 用户的权限由对数据库具有访问权限,但在数据库中没有用户帐户的用户继承。Permissions granted to the guest user are inherited by users who have access to the database, but who do not have a user account in the database. guest 用户无法删除,但可通过撤消其 CONNECT 权限禁用。The guest user cannot be dropped, but it can be disabled by revoking it's CONNECT permission. 可以通过在 mastertempdb 以外的任何数据库中执行 REVOKE CONNECT FROM GUEST; 来撤消 CONNECT 权限。The CONNECT permission can be revoked by executing REVOKE CONNECT FROM GUEST; within any database other than master or tempdb.

有关设计权限系统的信息,请参阅 Getting Started with Database Engine PermissionsFor information about designing a permissions system, see Getting Started with Database Engine Permissions.

下列主题包括在 SQL ServerSQL Server 联机丛书的本节中:The following topics are included in this section of SQL ServerSQL Server Books Online:

另请参阅See Also

保护 SQL Server Securing SQL Server
sys.database_principals (Transact-SQL) sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL) sys.server_principals (Transact-SQL)
sys.sql_logins (Transact-SQL) sys.sql_logins (Transact-SQL)
sys.database_role_members (Transact-SQL) sys.database_role_members (Transact-SQL)
服务器级别角色 Server-Level Roles
数据库级别的角色Database-Level Roles