Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.
管理 SQL Server 的安全性非常重要。作为 DBA 或安全管理员，您需要为登录名和数据库用户提供对 SQL Server 中资源的访问权限。SQL Server 同时具有可能需要授予访问权限的服务器和数据库资源。可以将对这些资源的访问权限授予单个登录帐户或数据库用户，也可以授予登录名或数据库用户可以是其成员的角色。通过角色授予访问权限称为基于角色的安全性。

There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.
有两种类型的角色：固定角色或用户定义角色。在本文中，我将讨论 SQL Server 提供的不同固定服务器和数据库角色，以及如何使用这些角色来支持基于角色的安全性，以简化对不同 SQL Server 资源的访问。在以后的文章中，我将讨论用户定义的服务器和数据库角色。

What is role-based security?
什么是基于角色的安全性？

Role-based security is the concept of providing logins and/or database users access to a SQL Server resource by being a member of a role. A role is an object in SQL Server that contains members, much like a Windows group contain members. When a login or database user is a member of a role, they inherit the role’s permissions.
基于角色的安全性是指通过成为角色的成员，为登录名和/或数据库用户提供对 SQL Server 资源的访问权限。角色是 SQL Server 中包含成员的对象，与 Windows 组包含成员非常相似。当登录名或数据库用户是角色的成员时，他们将继承角色的权限。

When role-based security is used, the actual access permissions to SQL Server resources are granted to a role and not a specific login or user. Role-based security reduces the amount of administration work needed to grant and manage security when multiple logins or users require the same access to SQL Server resources. Once a role has been set up, and the appropriate permissions are granted to it, it is just a simple matter of adding logins or users to the role to provide them with the same access as the role. Without using roles, an administrator would need to grant the same permissions to each login or database user, thus causing additional administration work. There is also the possibility of making an error which would result in some logins and users getting the wrong set of permissions.
使用基于角色的安全性时，将向角色授予对 SQL Server 资源的实际访问权限，而不是向特定登录名或用户授予访问权限。当多个登录名或用户需要对 SQL Server 资源的相同访问权限时，基于角色的安全性减少了授予和管理安全性所需的管理工作量。设置角色并授予其适当的权限后，只需向角色添加登录名或用户即可为其提供与角色相同的访问权限。如果不使用角色，管理员将需要向每个登录或数据库用户授予相同的权限，从而导致额外的管理工作。还有可能犯错误，这会导致某些登录和用户获得错误的权限集。

There are two types of fixed roles in SQL Server: Server and Database. The fixed server roles provide specific security access to server resources. In contrast, the fixed database roles provide access to database resources.
SQL Server 中有两种类型的固定角色：服务器和数据库。固定服务器角色提供对服务器资源的特定安全访问。相比之下，固定数据库角色提供对数据库资源的访问。

Fixed server roles  固定服务器角色

Fixed Server roles have server-wide scope. They come with a set of canned permissions tied to them. The permissions for server roles can’t be changed or extended.
固定 Server 角色具有 Server 范围。它们附带一组与之关联的固定权限。无法更改或扩展服务器角色的权限。

There are nine different fixed server roles provided with SQL Server, which are shown in Table 1, along with a description. The information in the table is directly from the Microsoft SQL Server documentation.
SQL Server 提供了 9 种不同的固定服务器角色，如表 1 所示，并附有说明。表中的信息直接来自 Microsoft SQL Server 文档。

Table 1: Fixed Server Roles
表 1：固定服务器角色

Each fixed server role provides a unique fixed set of permissions that can provide different kinds of access to server resources. The set of permissions associated with fixed server roles (with the exception of the public server role) cannot be modified.
每个固定服务器角色都提供一组唯一的固定权限，这些权限可以提供对服务器资源的不同类型的访问。无法修改与固定服务器角色（公共服务器角色除外）关联的权限集。

The public role is a little different from all other fixed server roles in that you can grant permissions to this role. When permissions are granted to the public role, all logins with access to SQL Server will inherit the permissions of the public role. The public role is a great way to provide some default permissions to every login.
public 角色与所有其他固定服务器角色略有不同，因为您可以向此角色授予权限。向 public 角色授予权限后，所有有权访问 SQL Server 的登录名都将继承 public 角色的权限。public 角色是为每个登录提供一些默认权限的好方法。

When a login, Windows account or Windows group becomes a member of one of these server roles, they inherit the rights associated with the role. If someone needs the same rights as a server role, it is much easier to make them a member of the role instead of granting them access to each of the permissions associated with a role. Additionally, when you want to grant the same server rights to multiple logins, placing each login in the same server roles makes it easy to accomplish this, ensuring they get exactly the same permissions. User-defined roles can also be added as members of a server role. I’ll leave the discussion about user-defined roles for another article.
当登录名、Windows 帐户或 Windows 组成为这些服务器角色之一的成员时，它们将继承与该角色关联的权限。如果某人需要与服务器角色相同的权限，则使他们成为该角色的成员比授予他们访问与角色关联的每个权限要容易得多。此外，当您希望将相同的服务器权限授予多个登录名时，将每个登录名置于相同的服务器角色中可以轻松实现此目的，从而确保它们获得完全相同的权限。用户定义的角色也可以添加为 server 角色的成员。我将把关于用户定义角色的讨论留到另一篇文章中讨论。

There are a number of different stored procedures, views and functions that can be used to work with fixed server roles. If you are unsure of the permissions associated with a server role, you can use the system stored procedures sp_srvrolepermission to displays the permission assigned to a single fixed server role or all the fixed server roles. The code in Listing 1 shows two different examples of how to use this stored procedure.
有许多不同的存储过程、视图和函数可用于处理固定服务器角色。如果不确定与服务器角色关联的权限，可以使用系统存储过程sp_srvrolepermission来显示分配给单个固定服务器角色或所有固定服务器角色的权限。清单 1 中的代码显示了如何使用此存储过程的两个不同示例。

Listing 1: Using sp_srvrolepermission
清单 1：使用 sp_srvrolepermission

For a complete list of all stored procedures, commands, views and functions that work with fixed server roles, you can check out the list by using this link.
有关使用固定服务器角色的所有存储过程、命令、视图和函数的完整列表，您可以使用此链接查看该列表。

Adding a login to a fixed server role can be done using SQL Server Management Studio (SSMS) or TSQL. To use SSMS, follow these steps:
可以使用 SQL Server Management Studio （SSMS） 或 TSQL 将登录名添加到固定服务器角色。要使用 SSMS，请执行以下步骤：

1. Connect to an instance  连接到实例
2. Expand the Security item
   展开 Security 项
3. Expand the Server Roles item
   展开 Server Roles 项
4. Right-click on the fixed server role and then click on the properties option
   右键单击固定服务器角色，然后单击属性选项
5. Click on the Add button on Server Roles Properties page
   单击 Server Roles Properties 页面上的 Add 按钮
6. Select the login or user-defined server role that you want to add to fixed server role
   选择要添加到固定服务器角色的登录服务器角色或用户定义的服务器角色
7. Click on a series of Ok buttons to complete the addition of new member to the role
   单击一系列 Ok 按钮以完成向角色添加新成员

Before clicking to add the member, the dialog should look like Figure 1.
在单击以添加成员之前，对话框应如图 1 所示。

Figure 1: Add a new login to a fixed server role.
图 1：向固定服务器角色添加新登录名。

Alternatively, you can use the TSQL code to add a login to a fixed server role. The code in Listing 2 adds the Red-Gate login to the sysadmin fixed server role.
或者，您可以使用 TSQL 代码将登录名添加到固定服务器角色。清单 2 中的代码将 Red-Gate 登录添加到 sysadmin 固定服务器角色。

Listing 2: Adding a login to the sysadmin fixed server role.
清单 2：向 sysadmin 固定服务器角色添加登录名。

Removing logins from a fixed server role can be done as easily as you added them by using SSMS or TSQL. The code in Listing 3 shows how to remove the Red-Gate login from the sysadmin fixed server role.
从固定服务器角色中删除登录名，就像使用 SSMS 或 TSQL 添加登录名一样简单。清单 3 中的代码显示了如何从 sysadmin 固定服务器角色中删除 Red-Gate 登录。

Listing 3: Removing login from sysadmin fixed server role.
清单 3：从 sysadmin 固定服务器角色中删除登录名。

Fixed server roles are a great way to provide DBAs, Security Admins, and operators access to the server resources they need to perform their job duties. By using server roles, you can simplify the granting of permissions to server resources. In addition to fixed server roles, there are also fixed database roles.
固定服务器角色是向 DBA、安全管理员和作员提供执行其工作职责所需的服务器资源访问权限的好方法。通过使用服务器角色，您可以简化对服务器资源的权限授予。除了固定的服务器角色之外，还有固定的数据库角色。

Database Roles  数据库角色

To help manage security at the database level, SQL Server has databases roles. Just like server roles, there are two different types of database roles: fixed and user-defined. Fixed database roles are just like fixed server roles, meaning they have a specific set of permissions associated with each one that cannot be altered. Fixed database roles only provide permissions to database resources in a specific database.
为了帮助管理数据库级别的安全性，SQL Server 提供了数据库角色。与服务器角色一样，有两种不同类型的数据库角色：固定数据库角色和用户定义数据库角色。固定数据库角色与固定服务器角色类似，这意味着它们具有一组与每个角色关联的特定权限，这些权限无法更改。固定数据库角色仅提供对特定数据库中数据库资源的权限。

If a database user is a member of a fixed database role, they inherit the permissions that have been pre-defined for the fixed database role. Each database contains the same set of fixed database roles. Table 2 contains the names and definitions for each fixed database role definition, as found in the Microsoft documentation.
如果数据库用户是固定数据库角色的成员，则他们将继承为固定数据库角色预定义的权限。每个数据库都包含相同的固定数据库角色集。表 2 包含每个固定数据库角色定义的名称和定义，如 Microsoft 文档所示。

Table 2: Fixed Database Roles
表 2：固定数据库角色

A few additional special fixed database roles only apply to the msdb database or SQL Database on Azure. For more information on these special roles, see Microsoft documentation here.
一些其他特殊的固定数据库角色仅适用于 Azure 上的 msdb 数据库或 SQL 数据库。有关这些特殊角色的更多信息，请参阅此处的 Microsoft 文档。

There is also the public database role. Just like the public server role, rights can be granted to the public database role. When rights have been granted to the public database role in a database, those rights are inherited by each database user that has been defined as a user of the database. The public role is a great way to provide the same permissions to database resources for every database user in a database.
还有 public database 角色。与公共服务器角色一样，可以向公共数据库角色授予权限。向数据库中的 public 数据库角色授予权限后，这些权限将由定义为数据库用户的每个数据库用户继承。public 角色是为数据库中的每个数据库用户提供对数据库资源的相同权限的好方法。

Not all organization will use each of these roles to provide access. Most shops use the db_datareader, and db_datawriter roles. If you make a database user a member of these roles, they will be able to read and/or update any user table in the database. Not only that, but they will also be able to update and/or read any data from any new user table that might be added in the future. This could be a good thing or a bad thing. It is a good thing if you want users to automatically gain read and/or update rights to all new user tables, regardless of the table. If you ever think you might want to add one or more tables to your database that only a few database users should have access to, then avoid using these two roles to provide blanket read and/or write access to database tables. Because these two roles automatically provide access to any new user table defined in the future, some shops prohibit the use of these roles to ensure database users only have access to the tables they need to perform their job function.
并非所有组织都会使用这些角色中的每一个来提供访问权限。大多数商店使用 db_datareader 和 db_datawriter 角色。如果您使数据库用户成为这些角色的成员，他们将能够读取和/或更新数据库中的任何用户表。不仅如此，他们还将能够更新和/或从将来可能添加的任何新用户表中读取任何数据。这可能是一件好事，也可能是一件坏事。如果您希望用户自动获得对所有新用户表的读取和/或更新权限，而不管表是什么，这是一件好事。如果您曾经认为您可能希望向数据库添加一个或多个只有少数数据库用户应该有权访问的表，请避免使用这两个角色来提供对数据库表的全面读取和/或写入访问权限。由于这两个角色会自动提供对将来定义的任何新用户表的访问权限，因此一些商店禁止使用这些角色，以确保数据库用户只能访问执行其工作职能所需的表。

Just like fixed server roles, there are a number of system stored procedures, commands, views, and functions that can be used to display and manage fixed database roles. The code in Listing 4 shows how to use one of those system stored procedures sp_dbfixedrolepermission, to display all the permissions associated with each fixed database role in the AdventureWorks2019 database, as well as how to use this stored procedure to display just the permissions associated with the single fixed database role db_datareader.
与固定服务器角色一样，有许多系统存储过程、命令、视图和函数可用于显示和管理固定数据库角色。清单 4 中的代码显示了如何使用其中一个系统存储过程sp_dbfixedrolepermission来显示与 AdventureWorks2019 数据库中的每个固定数据库角色关联的所有权限，以及如何使用此存储过程来仅显示与单个固定数据库角色db_datareader关联的权限。

Listing 4: Displaying permissions associated with fixed database roles
清单 4：显示与固定数据库角色关联的权限

For a complete list of all stored procedures, commands, views and functions used to display and manage fixed database roles you can review the documentation found here.
有关用于显示和管理固定数据库角色的所有存储过程、命令、视图和函数的完整列表，您可以查看此处的文档。

In order for a database user to inherit the permissions of a fixed database role, they need to be a member of a fixed database role. To determine if a databases user is a member of a database role, you can review the role membership using SSMS by following the steps:
为了使数据库用户继承固定数据库角色的权限，他们需要是固定数据库角色的成员。要确定数据库用户是否是数据库角色的成员，您可以按照以下步骤使用 SSMS 查看角色成员身份：

1. Connect to an instance  连接到实例
2. Expand Databases  展开数据库
3. Expand the database for which you want to review fixed database roles
   展开要查看其固定数据库角色的数据库
4. Expand the Security item  展开 Security 项
5. Expand the Roles item  展开 Roles 项
6. Expand the Database Roles Item
   展开 Database Roles 项
7. Double click on the role in which you want to see members
   双击要查看成员的角色
8. Review the properties window display to see the members of the role
   查看属性窗口显示以查看角色的成员

Figure 2 shows the members of the db_datareader role:
图 2 显示了 db_datareader 角色的成员：

Figure 2: Review fixed database role permissions
图 2：查看固定的数据库角色权限

An application might need to programmatically determine if a database user is a member of roles. The IS_MEMBER function allows you to write TSQL code to do that. Using this function would allow you to build an application that displays different menu options for different database users, depending on the database roles that the current user is a member. To programmatically determine if the currently logged-on database user is a member of db_datawriter role, in the AdventureWorks2019 database, you could use the code in Listing 5.
应用程序可能需要以编程方式确定数据库用户是否是角色的成员。IS_MEMBER 函数允许您编写 TSQL 代码来执行此作。使用此功能将允许您构建一个应用程序，该应用程序根据当前用户所属的数据库角色为不同的数据库用户显示不同的菜单选项。要以编程方式确定当前登录的数据库用户是否是db_datawriter角色的成员，在 AdventureWorks2019 数据库中，可以使用清单 5 中的代码。

Listing 5: Determining if current database user is a member of a role
清单 5：确定当前数据库用户是否是角色的成员

The IS_MEMBER function returns a 1 if the current user is a member of the db_datawriter role or 0 if the user is not a member of this role.
如果当前用户是 db_datawriter 角色的成员，则 IS_MEMBER 函数返回 1，如果用户不是此角色的成员，则返回 0。

Predefined Server or Database Roles
预定义的服务器或数据库角色

Several predefined server and database roles are provided in SQL Server. These predefined roles provide members with a fixed set of permissions based on the role. Using these predefined roles makes it easy to grant logins or database users access to a predefined set of permissions by just making them a member of a server or database role. One thing to keep in mind when using fixed server and database roles is that the permissions cannot be changed or expanded. Using predefined server and databases roles are a great way to provide a set of canned access to server and/or database resources with minimal administrative effort.
SQL Server 中提供了多个预定义的服务器和数据库角色。这些预定义角色根据角色为成员提供一组固定的权限。使用这些预定义角色，只需使登录名或数据库用户成为服务器或数据库角色的成员，即可轻松授予他们访问一组预定义权限的权限。使用固定服务器和数据库角色时要记住的一点是，权限不能更改或扩展。使用预定义的服务器和数据库角色是一种很好的方法，可以以最少的管理工作提供对服务器和/或数据库资源的一组标准访问。