控制和授予对 SQL 数据库和 SQL 数据仓库的数据库访问权限Controlling and granting database access to SQL Database and SQL Data Warehouse

配置防火墙规则后,可以使用数据库中的某个管理员帐户、数据库所有者或数据库用户的身份连接到 Azure SQL 数据库SQL 数据仓库After firewall rules configuration, you can connect to Azure SQL Database and SQL Data Warehouse as one of the administrator accounts, as the database owner, or as a database user in the database.

Note

本主题适用于 Azure SQL 服务器,也适用于在 Azure SQL 服务器中创建的 SQL 数据库和 SQL 数据仓库数据库。This topic applies to Azure SQL server, and to SQL Database and SQL Data Warehouse databases created on the Azure SQL server. 为简单起见,在提到 SQL 数据库和 SQL 数据仓库时,本文统称 SQL 数据库。For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.

Tip

有关教程,请参阅保护 Azure SQL 数据库For a tutorial, see Secure your Azure SQL Database. 本教程不适用于 Azure SQL 数据库托管实例 。This tutorial does not apply to Azure SQL Database Managed Instance.

非受限管理帐户Unrestricted administrative accounts

有两个充当管理员的管理帐户(服务器管理员Active Directory 管理员)。There are two administrative accounts (Server admin and Active Directory admin) that act as administrators. 若要为 SQL Server 标识这些管理员帐户,请打开 Azure 门户,然后导航到 SQL Server 或 SQL 数据库的“属性”选项卡。To identify these administrator accounts for your SQL server, open the Azure portal, and navigate to the Properties tab of your SQL server or SQL Database.

SQL Server 管理员

  • 服务器管理员Server admin

    创建 Azure SQL 服务器时,必须指定服务器管理员登录名When you create an Azure SQL server, you must designate a Server admin login. SQL 服务器创建该帐户作为 master 数据库中的登录名。SQL server creates that account as a login in the master database. 此帐户通过 SQL Server 身份验证(用户名和密码)进行连接。This account connects using SQL Server authentication (user name and password). 此类帐户只能存在一个。Only one of these accounts can exist.

    Note

    若要重置服务器管理员的密码,请转到 Azure 门户,单击“SQL Server”,从列表中选择服务器,然后单击“重置密码” 。To reset the password for the server admin, go to the Azure portal, click SQL Servers, select the server from the list, and then click Reset Password.

  • Azure Active Directory 管理员Azure Active Directory admin

    也可以将某个 Azure Active Directory 帐户(个人帐户或安全组帐户)配置为管理员。One Azure Active Directory account, either an individual or security group account, can also be configured as an administrator. 配置 Azure AD 管理员是选择性的,但如果需要使用 Azure AD 帐户连接到 SQL 数据库,则必须配置 Azure AD 管理员。 It is optional to configure an Azure AD administrator, but an Azure AD administrator must be configured if you want to use Azure AD accounts to connect to SQL Database. 有关配置 Azure Active Directory 访问权限的详细信息,请参阅使用 Azure Active Directory 身份验证连接到 SQL 数据库或 SQL 数据仓库SQL 数据库和 SQL 数据仓库针对 Azure AD MFA 的 SSMS 支持For more information about configuring Azure Active Directory access, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication and SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.

服务器管理员Azure AD 管理员帐户具有以下特征:The Server admin and Azure AD admin accounts have the following characteristics:

  • 只有这些帐户才能自动连接到服务器上的任何 SQL 数据库。Are the only accounts that can automatically connect to any SQL Database on the server. (其他帐户若要连接到用户数据库,它们必须是数据库的所有者,或者在用户数据库中具有相应的用户帐户。)(To connect to a user database, other accounts must either be the owner of the database, or have a user account in the user database.)
  • 这些帐户将以 dbo 用户的身份进入用户数据库,在用户数据库中拥有所有权限。These accounts enter user databases as the dbo user and they have all the permissions in the user databases. (用户数据库的所有者也以 dbo 用户的身份进入数据库。)(The owner of a user database also enters the database as the dbo user.)
  • 不会以 dbo 用户的身份进入 master 数据库,在 master 数据库中拥有受限的权限。Do not enter the master database as the dbo user, and have limited permissions in master.
  • 不是标准 SQL Server sysadmin 固定服务器角色的成员,SQL 数据库中未提供此角色。Are not members of the standard SQL Server sysadmin fixed server role, which is not available in SQL database.
  • 可以在 master 数据库和服务器级 IP 防火墙规则中创建、更改及删除数据库、登录名与用户。Can create, alter, and drop databases, logins, users in master, and server-level IP firewall rules.
  • 可以在 dbmanagerloginmanager 角色中添加和删除成员。Can add and remove members to the dbmanager and loginmanager roles.
  • 可以查看 sys.sql_logins 系统表。Can view the sys.sql_logins system table.

配置防火墙Configuring the firewall

为单个 IP 地址或地址范围配置服务器级防火墙后,SQL 服务器管理员Azure Active Directory 管理员可以连接到 master 数据库以及所有用户数据库。When the server-level firewall is configured for an individual IP address or range, the SQL server admin and the Azure Active Directory admin can connect to the master database and all the user databases. 初始服务器级防火墙可通过 Azure 门户PowerShellREST API 进行配置。The initial server-level firewall can be configured through the Azure portal, using PowerShell or using the REST API. 建立连接以后,还可以使用 Transact-SQL 配置其他服务器级 IP 防火墙规则。Once a connection is made, additional server-level IP firewall rules can also be configured by using Transact-SQL.

管理员访问路径Administrator access path

适当地配置服务器级防火墙后,SQL 服务器管理员Azure Active Directory 管理员可以使用 SQL Server Management Studio 或 SQL Server Data Tools 等客户端工具进行连接。When the server-level firewall is properly configured, the SQL server admin and the Azure Active Directory admin can connect using client tools such as SQL Server Management Studio or SQL Server Data Tools. 仅最新工具提供所有的特性和功能。Only the latest tools provide all the features and capabilities. 下图显示了这两个管理员帐户的典型配置。The following diagram shows a typical configuration for the two administrator accounts.

配置两个管理帐户

使用服务器级防火墙中的开放端口时,管理员可以连接到任何 SQL 数据库。When using an open port in the server-level firewall, administrators can connect to any SQL Database.

通过使用 SQL Server Management Studio 连接到数据库Connecting to a database by using SQL Server Management Studio

有关创建服务器、数据库和服务器级 IP 防火墙规则,以及使用 SQL Server Management Studio 查询数据库的演练,请参阅通过 Azure 门户和 SQL Server Management Studio 开始使用 Azure SQL 数据库服务器、数据库和防火墙规则For a walk-through of creating a server, a database, server-level IP firewall rules, and using SQL Server Management Studio to query a database, see Get started with Azure SQL Database servers, databases, and firewall rules by using the Azure portal and SQL Server Management Studio.

Important

建议始终使用最新版本的 Management Studio 以保持与 Azure 和 SQL 数据库的更新同步。It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Azure and SQL Database. 更新 SQL Server Management StudioUpdate SQL Server Management Studio.

其他服务器级管理角色Additional server-level administrative roles

Important

本部分不适用于 Azure SQL 数据库托管实例 ,因为这些角色特定于 Azure SQL 数据库 。This section does not apply to Azure SQL Database Managed Instance as these roles are specific to Azure SQL Database.

除了上述服务器级管理角色以外,SQL 数据库还在可以添加用户帐户的 master 数据库中提供了两个受限的管理角色,这些角色可授予创建数据库或管理登录名的权限。In addition to the server-level administrative roles discussed previously, SQL Database provides two restricted administrative roles in the master database to which user accounts can be added that grant permissions to either create databases or manage logins.

数据库创建者Database creators

其中一个管理角色是 dbmanager 角色。One of these administrative roles is the dbmanager role. 此角色的成员可以创建新数据库。Members of this role can create new databases. 如果要使用此角色,请在 master 数据库中创建一个用户,并将该用户添加到 dbmanager 数据库角色。To use this role, you create a user in the master database and then add the user to the dbmanager database role. 若要创建数据库,用户必须是基于 master 数据库中的 SQL Server 登录名的用户,或者是基于 Azure Active Directory 用户的已包含数据库用户。To create a database, the user must be a user based on a SQL Server login in the master database or contained database user based on an Azure Active Directory user.

  1. 使用管理员帐户连接到 master 数据库。Using an administrator account, connect to the master database.

  2. 使用 CREATE LOGIN 语句创建 SQL Server 身份验证登录名。Create a SQL Server authentication login, using the CREATE LOGIN statement. 示例语句:Sample statement:

    CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
    

    Note

    创建登录名或包含数据库用户时使用强密码。Use a strong password when creating a login or contained database user. 有关详细信息,请参阅强密码For more information, see Strong Passwords.

    为了提高性能,会暂时在数据库级别缓存登录名(服务器级主体)。To improve performance, logins (server-level principals) are temporarily cached at the database level. 若要刷新身份验证缓存,请参阅 DBCC FLUSHAUTHCACHETo refresh the authentication cache, see DBCC FLUSHAUTHCACHE.

  3. master 数据库中,使用 CREATE USER 语句创建用户。In the master database, create a user by using the CREATE USER statement. 该用户可以是 Azure Active Directory 身份验证包含数据库用户(如果你已针对 Azure AD 身份验证配置了环境),可以是 SQL Server 身份验证包含数据库用户,也可以是基于 SQL Server 身份验证登录名(在前一步骤中创建)的 SQL Server 身份验证用户。示例语句:The user can be an Azure Active Directory authentication contained database user (if you have configured your environment for Azure AD authentication), or a SQL Server authentication contained database user, or a SQL Server authentication user based on a SQL Server authentication login (created in the previous step.) Sample statements:

    CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER; -- To create a user with Azure Active Directory
    CREATE USER Ann WITH PASSWORD = '<strong_password>'; -- To create a SQL Database contained database user
    CREATE USER Mary FROM LOGIN Mary;  -- To create a SQL Server user based on a SQL Server authentication login
    
  4. 使用 ALTER ROLE 语句将新用户添加到 master 中的 dbmanager 数据库角色 。Add the new user, to the dbmanager database role in master using the ALTER ROLE statement. 示例语句:Sample statements:

    ALTER ROLE dbmanager ADD MEMBER Mary; 
    ALTER ROLE dbmanager ADD MEMBER [mike@contoso.com];
    

    Note

    dbmanager 是 master 数据库中的数据库角色,因此只能向该 dbmanager 角色添加数据库用户。The dbmanager is a database role in master database so you can only add a database user to the dbmanager role. 不能向数据库级角色添加服务器级登录名。You cannot add a server-level login to database-level role.

  5. 必要时,可将防火墙规则配置为允许新用户建立连接。If necessary, configure a firewall rule to allow the new user to connect. (可在现有防火墙规则中包括新用户。)(The new user might be covered by an existing firewall rule.)

现在,该用户可以连接到 master 数据库,并且可以创建新数据库。Now the user can connect to the master database and can create new databases. 创建数据库的帐户成为该数据库的所有者。The account creating the database becomes the owner of the database.

登录名管理器Login managers

另一个管理角色是登录管理员角色。The other administrative role is the login manager role. 此角色的成员可在 master 数据库中创建新登录名。Members of this role can create new logins in the master database. 如果需要,可以完成相同的步骤(创建登录名和用户,然后向 loginmanager 角色添加用户),使用户能够在 master 数据库中创建新登录名。If you wish, you can complete the same steps (create a login and user, and add a user to the loginmanager role) to enable a user to create new logins in the master. 通常不必要创建登录名,因为 Azure 建议使用包含的数据库用户在数据库级别进行身份验证,而不要使用基于登录名的用户。Usually logins are not necessary as Azure recommends using contained database users, which authenticate at the database-level instead of using users based on logins. 有关详细信息,请参阅包含的数据库用户 - 使数据库可移植For more information, see Contained Database Users - Making Your Database Portable.

非管理员用户Non-administrator users

非管理员帐户通常无需访问 master 数据库。Generally, non-administrator accounts do not need access to the master database. 使用 CREATE USER (Transact-SQL) 语句在数据库级别创建包含数据库用户。Create contained database users at the database level using the CREATE USER (Transact-SQL) statement. 该用户可以是 Azure Active Directory 身份验证包含数据库用户(如果你已针对 Azure AD 身份验证配置了环境),可以是 SQL Server 身份验证包含数据库用户,也可以是基于 SQL Server 身份验证登录名(在前一步骤中创建)的 SQL Server 身份验证用户。有关详细信息,请参阅包含的数据库用户 - 使数据库可移植The user can be an Azure Active Directory authentication contained database user (if you have configured your environment for Azure AD authentication), or a SQL Server authentication contained database user, or a SQL Server authentication user based on a SQL Server authentication login (created in the previous step.) For more information, see Contained Database Users - Making Your Database Portable.

如果要创建用户,请先连接到数据库,并执行如下所示的语句:To create users, connect to the database, and execute statements similar to the following examples:

CREATE USER Mary FROM LOGIN Mary; 
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

开始时,仅其中一个管理员或数据库所有者可以创建用户。Initially, only one of the administrators or the owner of the database can create users. 若要授权其他用户来创建新用户,可通过如下所示语句向该选定用户授予 ALTER ANY USER 权限:To authorize additional users to create new users, grant that selected user the ALTER ANY USER permission, by using a statement such as:

GRANT ALTER ANY USER TO Mary;

若要向其他用户授予对数据库的完全控制权限,可让这些用户成为 db_owner 固定数据库角色的成员 。To give additional users full control of the database, make them a member of the db_owner fixed database role.

在 Azure SQL 数据库中使用 ALTER ROLE 语句。In Azure SQL Database use the ALTER ROLE statement.

ALTER ROLE db_owner ADD MEMBER Mary;

在 Azure SQL 数据仓库中,使用 EXEC sp_addrolememberIn Azure SQL Data Warehouse use EXEC sp_addrolemember.

EXEC sp_addrolemember 'db_owner', 'Mary';

Note

创建基于 SQL 数据库服务器登录名的数据库用户的一个常见原因是用户需要访问多个数据库。One common reason to create a database user based on a SQL Database server login is for users that need access to multiple databases. 由于包含的数据库的用户都是单独的实体,因此每个数据库都维护其各自的用户及其密码。Since contained database users are individual entities, each database maintains its own user and its own password. 这可能会导致开销,因为用户必须记住每个数据库的密码,当必须为许多数据库更改多个密码时,这通常难以做到。This can cause overhead as the user must then remember each password for each database, and it can become untenable when having to change multiple passwords for many databases. 但是,当使用 SQL Server 登录名和高可用性(活动异地复制和故障转移组)时,必须手动在每台服务器上设置 SQL Server 登录名。However, when using SQL Server Logins and high availability (active geo-replication and failover groups), the SQL Server logins must be set manually at each server. 否则,数据库用户在发生故障转移后将不再映射到该服务器登录名,并且在故障转移后将无法访问数据库。Otherwise, the database user will no longer be mapped to the server login after a failover occurs, and will not be able to access the database post failover. 有关为异地复制配置登录名的详细信息,请参阅针对异地还原或故障转移配置和管理 Azure SQL 数据库的安全性For more information on configuring logins for geo-replication, please see Configure and manage Azure SQL Database security for geo-restore or failover.

配置数据库级防火墙Configuring the database-level firewall

最好是规定非管理员用户只能通过防火墙来访问所使用的数据库。As a best practice, non-administrator users should only have access through the firewall to the databases that they use. 可以使用 sp_set_database_firewall_rule 语句来配置数据库级防火墙,而不必通过服务器级防火墙来授权其 IP 地址访问所有数据库。Instead of authorizing their IP addresses through the server-level firewall and giving them access to all databases, use the sp_set_database_firewall_rule statement to configure the database-level firewall. 不能通过门户来配置数据库级防火墙。The database-level firewall cannot be configured by using the portal.

非管理员访问路径Non-administrator access path

对数据库级防火墙进行适当配置以后,数据库用户即可使用 SQL Server Management Studio 或 SQL Server Data Tools 这样的客户端工具进行连接。When the database-level firewall is properly configured, the database users can connect using client tools such as SQL Server Management Studio or SQL Server Data Tools. 仅最新工具提供所有的特性和功能。Only the latest tools provide all the features and capabilities. 下图显示了典型的非管理员访问路径。The following diagram shows a typical non-administrator access path.

非管理员访问路径

组和角色Groups and roles

有效的访问管理需要将权限分配到组和角色,而不是分配到单个用户。Efficient access management uses permissions assigned to groups and roles instead of individual users.

  • 使用 Azure Active Directory 身份验证时,请将 Azure Active Directory 用户放入 Azure Active Directory 组。When using Azure Active Directory authentication, put Azure Active Directory users into an Azure Active Directory group. 为该组创建包含数据库用户。Create a contained database user for the group. 将一个或多个数据库用户添加到数据库角色,然后向数据库角色分配权限Place one or more database users into a database role and then assign permissions to the database role.

  • 使用 SQL Server 身份验证时,请在数据库中创建包含的数据库用户。When using SQL Server authentication, create contained database users in the database. 将一个或多个数据库用户添加到数据库角色,然后向数据库角色分配权限Place one or more database users into a database role and then assign permissions to the database role.

数据库角色可以是内置的角色,例如 db_ownerdb_ddladmindb_datawriterdb_datareaderdb_denydatawriterdb_denydatareaderThe database roles can be the built-in roles such as db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, and db_denydatareader. db_owner 通常用于向部分用户授予完全权限。db_owner is commonly used to grant full permission to only a few users. 其他固定数据库角色可用于快速开发简单的数据库,但不建议用于大多数生产数据库。The other fixed database roles are useful for getting a simple database in development quickly, but are not recommended for most production databases. 例如,db_datareader 固定数据库角色授予用户对数据库中每个表的读取访问权限,这通常超出了必要的范畴。For example, the db_datareader fixed database role grants read access to every table in the database, which is usually more than is strictly necessary. 而如果先使用 CREATE ROLE 语句创建自己的用户定义数据库角色,再根据业务需要向每个角色授予所需的最低权限,则要合适得多。It is far better to use the CREATE ROLE statement to create your own user-defined database roles and carefully grant each role the least permissions necessary for the business need. 如果用户是多个角色的成员,则会聚合所有这些角色的权限。When a user is a member of multiple roles, they aggregate the permissions of them all.

权限Permissions

可以在 SQL 数据库中单独授予或拒绝 100 多种权限。There are over 100 permissions that can be individually granted or denied in SQL Database. 这些权限中,许多都是嵌套式的。Many of these permissions are nested. 例如,针对架构的 UPDATE 权限包括针对该架构中每个表的 UPDATE 权限。For example, the UPDATE permission on a schema includes the UPDATE permission on each table within that schema. 与大多数权限系统中的情况一样,拒绝某个权限将覆盖对该权限的授予操作。As in most permission systems, the denial of a permission overrides a grant. 考虑到权限的嵌套性质和数目,可能需要进行仔细的研究才能设计出适当的权限系统,以便对数据库进行恰当的保护。Because of the nested nature and the number of permissions, it can take careful study to design an appropriate permission system to properly protect your database. 一开始可以了解权限(数据库引擎)中的权限列表,然后查看这些权限的海报大小的图Start with the list of permissions at Permissions (Database Engine) and review the poster size graphic of the permissions.

注意事项和限制Considerations and restrictions

管理 SQL 数据库中的登录名和用户时,请注意以下事项:When managing logins and users in SQL Database, consider the following:

  • 执行 CREATE/ALTER/DROP DATABASE 语句时,必须连接到 master 数据库。You must be connected to the master database when executing the CREATE/ALTER/DROP DATABASE statements.

  • 不能更改或删除与 服务器管理员 登录名相对应的数据库用户。The database user corresponding to the Server admin login cannot be altered or dropped.

  • 美国英语是 服务器管理员 登录名的默认语言。US-English is the default language of the Server admin login.

  • 只有管理员(服务器管理员登录名或 Azure AD 管理员)和 master 数据库中 dbmanager 数据库角色的成员才有权执行 CREATE DATABASEDROP DATABASE 语句。Only the administrators (Server admin login or Azure AD administrator) and the members of the dbmanager database role in the master database have permission to execute the CREATE DATABASE and DROP DATABASE statements.

  • 执行 CREATE/ALTER/DROP LOGIN 语句时,必须连接到 master 数据库。You must be connected to the master database when executing the CREATE/ALTER/DROP LOGIN statements. 但不建议使用登录名。However using logins is discouraged. 改用包含的数据库用户。Use contained database users instead.

  • 若要连接到用户数据库,必须在连接字符串中提供数据库的名称。To connect to a user database, you must provide the name of the database in the connection string.

  • 只有服务器级别主体登录名和 master 数据库中loginmanager 数据库角色的成员才有权执行 CREATE LOGINALTER LOGINDROP LOGIN 语句。Only the server-level principal login and the members of the loginmanager database role in the master database have permission to execute the CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements.

  • 在 ADO.NET 应用程序中执行 CREATE/ALTER/DROP LOGINCREATE/ALTER/DROP DATABASE 语句时,不允许使用参数化命令。When executing the CREATE/ALTER/DROP LOGIN and CREATE/ALTER/DROP DATABASE statements in an ADO.NET application, using parameterized commands is not allowed. 有关详细信息,请参阅 命令和参数For more information, see Commands and Parameters.

  • 在执行 CREATE/ALTER/DROP DATABASECREATE/ALTER/DROP LOGIN 语句时,上述每个语句都必须是 Transact-SQL 批中的唯一语句。When executing the CREATE/ALTER/DROP DATABASE and CREATE/ALTER/DROP LOGIN statements, each of these statements must be the only statement in a Transact-SQL batch. 否则会出错。Otherwise, an error occurs. 例如,以下 Transact-SQL 检查该数据库是否存在。For example, the following Transact-SQL checks whether the database exists. 如果该数据库存在,则调用 DROP DATABASE 语句删除该数据库。If it exists, a DROP DATABASE statement is called to remove the database. 因为 DROP DATABASE 语句不是该批处理中的唯一语句,所以执行以下 Transact-SQL 会导致错误。Because the DROP DATABASE statement is not the only statement in the batch, executing the following Transact-SQL statement results in an error.

    IF EXISTS (SELECT [name]
             FROM   [sys].[databases]
             WHERE  [name] = N'database_name')
    DROP DATABASE [database_name];
    GO
    

    请改用以下 Transact-SQL 语句:Instead, use the following Transact-SQL statement:

    DROP DATABASE IF EXISTS [database_name]
    
  • 在使用 FOR/FROM LOGIN 选项执行 CREATE USER 语句时,该语句必须是 Transact-SQL 批中的唯一语句。When executing the CREATE USER statement with the FOR/FROM LOGIN option, it must be the only statement in a Transact-SQL batch.

  • 在使用 WITH LOGIN 选项执行 ALTER USER 语句时,该语句必须是 Transact-SQL 批处理中的唯一语句。When executing the ALTER USER statement with the WITH LOGIN option, it must be the only statement in a Transact-SQL batch.

  • 若要执行 CREATE/ALTER/DROP 操作,用户需要对数据库拥有 ALTER ANY USER 权限。To CREATE/ALTER/DROP a user requires the ALTER ANY USER permission on the database.

  • 在数据库角色的所有者尝试在该数据库角色中添加或删除其他数据库用户时,可能会发生以下错误:“此数据库中不存在用户或角色‘Name’” 。When the owner of a database role tries to add or remove another database user to or from that database role, the following error may occur: User or role 'Name' does not exist in this database. 在用户对所有者不可见时,将会发生此错误。This error occurs because the user is not visible to the owner. 若要解决此问题,请向角色所有者授予对该用户的 VIEW DEFINITION 权限。To resolve this issue, grant the role owner the VIEW DEFINITION permission on the user.

后续步骤Next steps