数据库引擎权限入门Getting Started with Database Engine Permissions

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

数据库引擎Database Engine 中的权限通过登录名和服务器角色在服务器级别进行管理,以及通过数据库用户和数据库角色在数据库级别进行管理。Permissions in the 数据库引擎Database Engine are managed at the server level through logins and server roles, and at the database level through database users and database roles. SQL 数据库SQL Database 的模型在每个数据库中公开同一系统,但服务器级别权限不可用。The model for SQL 数据库SQL Database exposes the same system within each database, but the server level permissions are not available. 本主题复习一些基本的安全概念,然后介绍典型的权限实现。This topic reviews some basic security concepts and then describes a typical implementation of the permissions.

安全主体Security Principals

安全主体是使用 SQL ServerSQL Server 并可以为其分配执行操作的权限的标识的正式名称。Security principal is the official name of the identities that use SQL ServerSQL Server and that can be assigned permission to take actions. 它们通常是人员或人员组,但可以是伪装成人员的其他实体。They are usually people or groups of people, but can be other entities that pretend to be people. 安全主体可以使用列出的 Transact-SQLTransact-SQL 或通过使用 SQL Server Management StudioSQL Server Management Studio来进行创建和管理。The security principals can be created and managed using the Transact-SQLTransact-SQL listed, or by using SQL Server Management StudioSQL Server Management Studio.

登录名Logins

登录名是用于登录到 SQL Server 数据库引擎SQL Server Database Engine的单个用户帐户。Logins are individual user accounts for logging on to the SQL Server 数据库引擎SQL Server Database Engine. SQL ServerSQL Server SQL 数据库SQL Database 支持基于 Windows 身份验证的登录名和基于 SQL ServerSQL Server 身份验证的登录名。and SQL 数据库SQL Database support logins based on Windows authentication and logins based on SQL ServerSQL Server authentication. 有关这两种类型的登录名的信息,请参阅 Choose an Authentication ModeFor information about the two types of logins, see Choose an Authentication Mode.

固定服务器角色Fixed Server Roles

SQL ServerSQL Server中,固定服务器角色是一组预配置的角色,便于对服务器级别权限进行分组。In SQL ServerSQL Server, fixed server roles are a set of pre-configured roles that provide convenient group of server-level permissions. 可以使用 ALTER SERVER ROLE ... ADD MEMBER 语句将登录名添加到角色。Logins can be added to the roles using the ALTER SERVER ROLE ... ADD MEMBER statement. 有关详细信息,请参阅 ALTER SERVER ROLE (Transact-SQL)For more information, see ALTER SERVER ROLE (Transact-SQL). SQL 数据库SQL Database 不支持固定服务器角色,但在 master 数据库中有两个角色(dbmanagerloginmanager)充当服务器角色。does not support the fixed server roles, but has two roles in the master database (dbmanager and loginmanager) that act like server roles.

用户定义的服务器角色User-defined Server Roles

SQL ServerSQL Server中,可以创建你自己的服务器角色并向它们分配服务器级权限。In SQL ServerSQL Server, you can create your own server roles and assign server-level permissions to them. 可以使用 ALTER SERVER ROLE ... ADD MEMBER 语句将登录名添加到服务器角色。Logins can be added to the server roles using the ALTER SERVER ROLE ... ADD MEMBER statement. 有关详细信息,请参阅 ALTER SERVER ROLE (Transact-SQL)For more information, see ALTER SERVER ROLE (Transact-SQL). SQL 数据库SQL Database 不支持用户定义的服务器角色。does not support the user-defined server roles.

数据库用户Database Users

通过在数据库中创建数据库用户并将该数据库用户映射到登录名来授予登录名对数据库的访问权限。Logins are granted access to a database by creating a database user in a database and mapping that database user to login. 通常,数据库用户名与登录名相同,尽管它不必要相同。Typically the database user name is the same as the login name, though it does not have to be the same. 每个数据库用户均映射到单个登录名。Each database user maps to a single login. 一个登录名只能映射到数据库中的一个用户,但可以映射为多个不同数据库中的数据库用户。A login can be mapped to only one user in a database, but can be mapped as a database user in several different databases.

也可以创建不具有相应登录名的数据库用户。Database users can also be created that do not have a corresponding login. 这些数据库用户称为“包含的数据库用户” 。These are called contained database users. MicrosoftMicrosoft 鼓励使用包含的数据库用户,因为这样可以更轻松地将你的数据库移到另一个服务器。encourages the use of contained database users because it makes it easier to move your database to a different server. 与登录名类似,包含的数据库用户可以使用 Windows 身份验证或 SQL ServerSQL Server 身份验证。Like a login, a contained database user can use either Windows authentication or SQL ServerSQL Server authentication. 有关详细信息,请参阅 包含的数据库用户 - 使你的数据库可移植For more information, see Contained Database Users - Making Your Database Portable.

有 12 种类型的用户,它们在如何进行身份验证以及所表示的人员方面略有差异。There are 12 types of users with slight differences in how they authenticate, and who they represent. 若要查看用户列表,请参阅 CREATE USER (Transact-SQL)To see a list of users, see CREATE USER (Transact-SQL).

固定数据库角色Fixed Database Roles

固定数据库角色是一组预配置的提供方便的数据库级权限组的角色。Fixed database roles are a set of pre-configured roles that provide convenient group of database-level permissions. 可以使用 ALTER ROLE ... ADD MEMBER 语句将数据库用户和用户定义的数据库角色添加到固定数据库角色。Database users and user-defined database roles can be added to the fixed database roles using the ALTER ROLE ... ADD MEMBER statement. 有关详细信息,请参阅 ALTER ROLE (Transact-SQL)For more information, see ALTER ROLE (Transact-SQL).

用户定义的数据库角色User-defined Database Roles

具有 CREATE ROLE 权限的用户可以创建新的用户定义的数据库角色来表示具有常用权限的用户组。Users with the CREATE ROLE permission can create new user-defined database roles to represent groups of users with common permissions. 通常对整个角色授予或拒绝权限,从而简化了权限管理和监视。Typically permissions are granted or denied to the entire role, simplifying permissions management and monitoring. 可以使用 ALTER ROLE ... ADD MEMBER 语句向数据库角色添加数据库用户。Database users can be added to the database roles by using the ALTER ROLE ... ADD MEMBER statement. 有关详细信息,请参阅 ALTER ROLE (Transact-SQL)For more information, see ALTER ROLE (Transact-SQL).

其他主体Other principals

这里不讨论的其他安全主体包括基于证书或非对称密钥的应用程序角色、登录名和用户。Additional security principals not discussed here include application roles, and logins and users based on certificates or asymmetric keys.

有关显示 Windows 用户、Windows 组、登录名和数据库用户之间关系的图形,请参阅 Create a Database UserFor a graphic showing the relationships between Windows users, Windows groups, logins, and database users, see Create a Database User.

典型方案Typical Scenario

下面的示例表示配置权限的常见和建议的方法。The following example represents a common and recommended method of configuring permissions.

在 Active Directory 或 Azure Active Directory 中:In Active Directory or Azure Active Directory:

  1. 为每个人员创建一个 Windows 用户。Create a Windows user for each person.

  2. 创建表示工作单位和工作职能的 Windows 组。Create Windows groups that represent the work units and the work functions.

  3. 将 Windows 用户添加到 Windows 组。Add the Windows users to the Windows groups.

如果连接的人员将连接到多个数据库If the person connecting will be connecting to many databases

  1. 为 Windows 组创建登录名。Create a login for the Windows groups. (如果使用 SQL ServerSQL Server 身份验证,请跳过 Active Directory 步骤,并在此处创建 SQL ServerSQL Server 身份验证登录名。)(If using SQL ServerSQL Server authentication, skip the Active Directory steps, and create SQL ServerSQL Server authentication logins here.)

  2. 在用户数据库中,为表示 Windows 组的登录名创建一个数据库用户。In the user database, create a database user for the login representing the Windows groups.

  3. 在用户数据库中,创建一个或多个用户定义的数据库角色,每个角色表示相似的职能。In the user database, create one or more user-defined database roles, each representing a similar function. 例如,财务分析人员和销售分析人员。For example financial analyst, and sales analyst.

  4. 将数据库用户添加到一个或多个用户定义的数据库角色。Add the database users to one or more user-defined database roles.

  5. 向用户定义的数据库角色授予权限。Grant permissions to the user-defined database roles.

如果连接的人员将只连接到一个数据库If the person connecting will be connecting to only one database

  1. 为 Windows 组创建登录名。Create a login for the Windows groups. (如果使用 SQL ServerSQL Server 身份验证,请跳过 Active Directory 步骤,并在此处创建 SQL ServerSQL Server 身份验证登录名。)(If using SQL ServerSQL Server authentication, skip the Active Directory steps, and create SQL ServerSQL Server authentication logins here.)

  2. 在用户数据库中,为 Windows 组创建一个包含的数据库用户。In the user database, create a contained database user for the Windows group. (如果使用 SQL ServerSQL Server 身份验证,请跳过 Active Directory 步骤,并在此处创建包含的数据库用户 SQL ServerSQL Server 身份验证。)(If using SQL ServerSQL Server authentication, skip the Active Directory steps, and create contained database user SQL ServerSQL Server authentication here.

  3. 在用户数据库中,创建一个或多个用户定义的数据库角色,每个角色表示相似的职能。In the user database, create one or more user-defined database roles, each representing a similar function. 例如,财务分析人员和销售分析人员。For example financial analyst, and sales analyst.

  4. 将数据库用户添加到一个或多个用户定义的数据库角色。Add the database users to one or more user-defined database roles.

  5. 向用户定义的数据库角色授予权限。Grant permissions to the user-defined database roles.

此时的典型结果是,Windows 用户是 Windows 组的成员。The typical result at this point, is that a Windows user is a member of a Windows group. Windows 组在 SQL ServerSQL ServerSQL 数据库SQL Database中具有登录名。The Windows group has a login in SQL ServerSQL Server or SQL 数据库SQL Database. 该登录名将映射到用户数据库中的用户标识。The login is mapped to a user identity in the user-database. 用户是数据库角色的成员。The user is a member of a database role. 现在,你需要将权限添加到角色。Now you need to add permissions to the role.

分配权限Assigning Permissions

大多数权限语句具有以下格式:Most permission statements have the format:

AUTHORIZATION  PERMISSION  ON  SECURABLE::NAME  TO  PRINCIPAL;  
  • AUTHORIZATION 必须为 GRANTREVOKEDENYAUTHORIZATION must be GRANT, REVOKE or DENY.

  • PERMISSION 确立允许或禁止哪个操作。The PERMISSION establishes what action is allowed or prohibited. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 可以指定 230 种权限。can specify 230 permissions. SQL 数据库SQL Database 具有较少的权限,因为某些操作在 Azure 中不相关。has fewer permissions because some actions are not relevant in Azure. 这些权限在权限(数据库引擎)主题和下面引用的图表中列出。The permissions are listed in the topic Permissions (Database Engine) and in the chart referenced below.

  • ON SECURABLE::NAME 是安全对象(服务器、服务器对象、数据库或数据库对象)的类型及其名称。ON SECURABLE::NAME is the type of securable (server, server object, database, or database object) and its name. 某些权限不需要 ON SECURABLE::NAME ,因为它是明确的或在上下文中不适当。Some permissions do not require ON SECURABLE::NAME because it is unambiguous or inappropriate in the context. 例如,CREATE TABLE 权限不需要 ON SECURABLE::NAME 子句。For example the CREATE TABLE permission doesn't require the ON SECURABLE::NAME clause. (例如, GRANT CREATE TABLE TO Mary; 允许 Mary 创建表。)(For example GRANT CREATE TABLE TO Mary; allows Mary to create tables.)

  • PRINCIPAL 是获得或失去权限的安全主体(登录名、用户或角色)。PRINCIPAL is the security principal (login, user, or role) which receives or loses the permission. 尽可能向角色授予权限。Grant permissions to roles whenever possible.

下面的示例 grant 语句将对 UPDATE 架构中包含的 Parts 表或视图的 Production 权限授予名为 PartsTeam的角色:The following example grant statement, grants the UPDATE permission on the Parts table or view which is contained in the Production schema to the role named PartsTeam:

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

使用 GRANT 语句向安全主体(登录名、用户和角色)授予权限。Permissions are granted to security principals (logins, users, and roles) by using the GRANT statement. 使用 DENY 命令显式拒绝权限。Permissions are explicitly denied by using the DENY command. 使用 REVOKE 语句删除以前授予或拒绝的权限。A previously granted or denied permission is removed by using the REVOKE statement. 权限是累积的,用户将获得授予该用户、登录名和任何组成员身份的所有权限;但是任何权限拒绝将覆盖所有授予。Permissions are cumulative, with the user receiving all the permissions granted to the user, login, and any group memberships; however any permission denial overrides all grants.

提示

一个常见错误是尝试使用 GRANT 而不是 DENY 来删除 REVOKEA common mistake is to attempt to remove a GRANT by using DENY instead of REVOKE. 当用户从多个源获得权限时,这会导致问题;此错误相当常见。This can cause problems when a user receives permissions from multiple sources; which is quite common. 以下示例演示了主体。The following example demonstrates the principal.

Sales 组通过语句 SELECT 获得对 OrderStatus 表的 GRANT SELECT ON OBJECT::OrderStatus TO Sales;权限。The Sales group receives SELECT permissions on the OrderStatus table through the statement GRANT SELECT ON OBJECT::OrderStatus TO Sales;. 用户 Ted 是 Sales 角色的成员。User Ted is a member of the Sales role. 还通过语句 SELECT 在 Ted 自己的用户名下授予 Ted 对 OrderStatus 表的 GRANT SELECT ON OBJECT::OrderStatus TO Ted;权限。Ted has also been granted SELECT permission to the OrderStatus table under his own user name through the statement GRANT SELECT ON OBJECT::OrderStatus TO Ted;. 假设管理员希望删除对 Sales 角色的 GRANTPresume the administer wishes to remove the GRANT to the Sales role.

  • 如果管理员正确执行 REVOKE SELECT ON OBJECT::OrderStatus TO Sales;,则 Ted 将通过其个人 SELECT 语句保留对 OrderStatus 表的 GRANT 访问权限。If the administrator correctly executes REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, then Ted will retain SELECT access to the OrderStatus table through his individual GRANT statement.

  • 如果管理员未正确执行 DENY SELECT ON OBJECT::OrderStatus TO Sales; ,则 Ted 作为 Sales 角色的成员将被拒绝 SELECT 权限,因为对 Sales 的 DENY 将覆盖其个人 GRANTIf the administrator incorrectly executes DENY SELECT ON OBJECT::OrderStatus TO Sales; then Ted, as a member of the Sales role, will be denied the SELECT permission because the DENY to Sales overrides his individual GRANT.

备注

可以使用 Management StudioManagement Studio配置权限。Permissions can be configured using Management StudioManagement Studio. 在对象资源管理器中查找安全对象,右键单击该安全对象,然后单击“属性” 。Find the securable in Object Explorer, right-click the securable, and then click Properties. 选择“权限”页 。Select the Permissions page. 有关使用权限页的帮助,请参阅 Permissions or Securables PageFor help on using the permission page, see Permissions or Securables Page.

权限层次结构Permission Hierarchy

权限具有父/子层次结构。Permissions have a parent/child hierarchy. 也就是说,如果你授予对数据库的 SELECT 权限,则该权限包括对数据库中所有(子)架构的 SELECT 权限。That is, if you grant SELECT permission on a database, that permission includes SELECT permission on all (child) schemas in the database. 如果你授予对架构的 SELECT 权限,则该权限包括对架构中所有(子)表和视图的 SELECT 权限。If you grant SELECT permission on a schema, it includes SELECT permission on all the (child) tables and views in the schema. 权限是可传递的;也就是说,如果你授予对数据库的 SELECT 权限,则该权限包括对所有(子级)架构和所有(孙级)表和视图的 SELECT 权限。The permissions are transitive; that is, if you grant SELECT permission on a database, it includes SELECT permission on all (child) schemas, and all (grandchild) tables and views.

权限还可以涵盖权限。Permissions also have covering permissions. 对某个对象的 CONTROL 权限通常为你提供对该对象的所有其他权限。The CONTROL permission on an object, normally gives you all other permissions on the object.

由于父/子层次结构和包含的层次结构可以作用于相同的权限,因此权限系统可以变得很复杂。Because both the parent/child hierarchy and the covering hierarchy can act on the same permission, the permission system can get complicated. 例如,让我们以数据库 (SalesDB) 的架构 (Customers) 中的表 (Region) 为例。For example, let's take a table (Region), in a schema (Customers), in a database (SalesDB).

  • CONTROL 权限包括对表 Region 的所有其他权限,包括 ALTERSELECTINSERTUPDATEDELETE、 and some other permissions.CONTROL permission on table Region includes all the other permissions on the table Region, including ALTER, SELECT, INSERT, UPDATE, DELETE, and some other permissions.

  • SELECT 包括对 Region 表的 SELECT 权限。SELECT on the Customers schema that owns the Region table includes the SELECT permission on the Region table.

因此,对 Region 表的 SELECT 权限可以通过以下六个语句中的任一个实现:So SELECT permission on the Region table can be achieved through any of these six statements:

GRANT SELECT ON OBJECT::Region TO Ted;   
  
GRANT CONTROL ON OBJECT::Region TO Ted;   
  
GRANT SELECT ON SCHEMA::Customers TO Ted;   
  
GRANT CONTROL ON SCHEMA::Customers TO Ted;   
  
GRANT SELECT ON DATABASE::SalesDB TO Ted;   
  
GRANT CONTROL ON DATABASE::SalesDB TO Ted;  

授予最少权限Grant the Least Permission

上面列出的第一个权限 (GRANT SELECT ON OBJECT::Region TO Ted;) 的粒度最细,这就是说,该语句是授予 SELECT的可能的最少权限。The first permission listed above (GRANT SELECT ON OBJECT::Region TO Ted;) is the most granular, that is, that statement is the least permission possible that grants the SELECT. 没有向它附带的从属对象授予权限。No permissions to subordinate objects come with it. 好的主体始终会授予可能的最少权限,但(与此相反)以较高级别授权以简化授权系统。It's a good principal to always grant the least permission possible, but (contradicting that) grant at higher levels in order to simplify the granting system. 因此,如果 Ted 需要对整个架构的权限,则只需在架构级别授予 SELECT 一次,而不是在表或视图级别授予 SELECT 多次。So if Ted needs permissions to the entire schema, grant SELECT once at the schema level, instead of granting SELECT at the table or view level many times. 数据库的设计对此策略可以有多成功的影响很大。The design of the database has a great deal of impact on how successful this strategy can be. 当数据库设计为需要相同权限的对象都包含在单个架构中时,此策略最有效。This strategy will work best when your database is designed so that objects needing identical permissions are included in a single schema.

权限的列表List of Permissions

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 具有 230 个权限。has 230 permissions. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 拥有 219 个权限。has 219 permissions. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 拥有 214 个权限。has 214 permissions. SQL Server 2008 R2SQL Server 2008 R2 拥有 195 个权限。has 195 permissions. SQL 数据库SQL DatabaseSQL 数据仓库SQL Data Warehouse分析平台系统Analytics Platform System 拥有较少的权限,因为他们仅公开数据库引擎的一部分,且一些权限并不适用于 SQL ServerSQL Server, SQL 数据仓库SQL Data Warehouse, and 分析平台系统Analytics Platform System have fewer permissions because they expose only a portion of the database engine, though each have some permissions that do not apply to SQL ServerSQL Server.

下图显示了权限以及它们彼此之间的关系。The following graphic shows the permissions and their relationships to each other. 多次列出了某些更高级别的权限(如 CONTROL SERVER)。Some of the higher level permissions (such as CONTROL SERVER) are listed many times. 在本文中,海报太小了,因此无法查看。In this article, the poster is far too small to read. 单击图像下载 pdf 格式的数据库引擎权限文章Click the image to download the Database Engine Permissions Poster in pdf format.

数据库引擎权限Database Engine Permissions

有关显示 数据库引擎Database Engine 主体与服务器和数据库对象之间关系的图形,请参阅权限层次结构(数据库引擎)For a graphic showing the relationships among the 数据库引擎Database Engine principals and server and database objects, see Permissions Hierarchy (Database Engine).

权限与固定服务器和固定数据库角色Permissions vs. Fixed Server and Fixed Database Roles

固定服务器角色和固定数据库角色的权限很相似,但与细粒度的权限并不完全相同。The permissions of the fixed server roles and fixed database roles are similar but not exactly the same as the granular permissions. 例如, sysadmin 固定服务器角色的成员在 SQL ServerSQL Server的实例上具有所有权限,如同使用 CONTROL SERVER 权限登录一样。For example, members of the sysadmin fixed server role have all permissions on the instance of SQL ServerSQL Server, as do logins with the CONTROL SERVER permission. 但是授予 CONTROL SERVER 权限不会使登录名成为 sysadmin 固定服务器角色的成员,将登录名添加到 sysadmin 固定服务器角色不会显式授予登录名 CONTROL SERVER 权限。But granting the CONTROL SERVER permission does not make a login a member of the sysadmin fixed server role, and adding a login to the sysadmin fixed server role does not explicitly grant the login the CONTROL SERVER permission. 有时存储过程将通过检查固定角色而不检查细粒度的权限来检查权限。Sometimes a stored procedure will check permissions by checking the fixed role and not checking the granular permission. 例如,分离数据库需要具有 db_owner 固定数据库角色中的成员身份。For example detaching a database requires membership in the db_owner fixed database role. 等效的 CONTROL DATABASE 权限并不够。The equivalent CONTROL DATABASE permission is not enough. 这两个系统并行运行,但彼此很少进行交互。These two systems operate in parallel but rarely interact with each other. Microsoft 建议尽可能使用更新的细粒度权限系统,而不是使用固定角色。Microsoft recommends using the newer, granular permission system instead of the fixed roles whenever possible.

监视权限Monitoring Permissions

以下视图返回安全信息。The following views return security information.

  • 可以使用 sys.server_principals 视图查看服务器上的登录名和用户定义的服务器角色。The logins and user-defined server roles on a server can be examined by using the sys.server_principals view. 此视图在 SQL 数据库SQL Database中不可用。This view is not available in SQL 数据库SQL Database.

  • 可以使用 sys.database_principals 视图查看数据库中的用户和用户定义的角色。The users and user-defined roles in a database can be examined by using the sys.database_principals view.

  • 可以使用 sys.server_permissions 视图查看授予登录名和用户定义的固定服务器角色的权限。The permissions granted to logins and user-defined fixed server roles can be examined by using the sys.server_permissions view. 此视图在 SQL 数据库SQL Database中不可用。This view is not available in SQL 数据库SQL Database.

  • 可以使用 sys.database_permissions 视图查看授予用户和用户定义的固定数据库角色的权限。The permissions granted to users and user-defined fixed database roles can be examined by using the sys.database_permissions view.

  • 可以使用 sys. sys.database_role_members 视图查看数据库角色成员身份。Database role membership can be examined by using the sys. sys.database_role_members view.

  • 可以使用 sys.server_role_members 视图查看服务器角色成员身份。Server role membership can be examined by using the sys.server_role_members view. 此视图在 SQL 数据库SQL Database中不可用。This view is not available in SQL 数据库SQL Database.

  • 有关更多与安全性相关的视图,请参阅 安全性目录视图 (Transact-SQL) 来进行创建和管理。For additional security related views, see Security Catalog Views (Transact-SQL) .

有用的 Transact-SQL 语句Useful Transact-SQL Statements

以下语句返回有关权限的有用信息。The following statements return useful information about permissions.

若要返回在数据库(SQL ServerSQL ServerSQL 数据库SQL Database)中授予或拒绝的显式权限,请在数据库中执行以下语句。To return the explicit permissions granted or denied in a database ( SQL ServerSQL Server and SQL 数据库SQL Database), execute the following statement in the database.

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

若要返回服务器角色的成员(仅限 SQL ServerSQL Server),请执行以下语句。To return the members of the server roles ( SQL ServerSQL Server only), execute the following statement.

SELECT sRole.name AS [Server Role Name] , sPrinc.name AS [Members]  
FROM sys.server_role_members AS sRo  
JOIN sys.server_principals AS sPrinc  
    ON sRo.member_principal_id = sPrinc.principal_id  
JOIN sys.server_principals AS sRole  
    ON sRo.role_principal_id = sRole.principal_id;  

若要返回数据库角色的成员(SQL ServerSQL ServerSQL 数据库SQL Database),请在数据库中执行以下语句。To return the members of the database roles ( SQL ServerSQL Server and SQL 数据库SQL Database), execute the following statement in the database.

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

Next StepsNext Steps

有关可帮助你入门的更多主题,请参阅:For more topics to get you started, see:

另请参阅See Also

SQL Server 数据库引擎和 Azure SQL Database 的安全中心 Security Center for SQL Server Database Engine and Azure SQL Database
安全函数 (Transact-SQL) Security Functions (Transact-SQL)
与安全性相关的动态管理视图和函数 (Transact-SQL) Security-Related Dynamic Management Views and Functions (Transact-SQL)
安全性目录视图 (Transact-SQL) Security Catalog Views (Transact-SQL)
sys.fn_builtin_permissions (Transact-SQL) sys.fn_builtin_permissions (Transact-SQL)
确定有效的数据库引擎权限Determining Effective Database Engine Permissions