CREATE USER (Transact-SQL)CREATE USER (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

向当前数据库添加用户。Adds a user to the current database. 下面列出了 12 种类型的用户,并给出了最基本的语法示例:The 12 types of users are listed below with a sample of the most basic syntax:

基于 master 数据库中登录名的用户 - 这是最常见的一种用户类型。Users based on logins in master - This is the most common type of user.

  • 基于 Windows Active Directory 帐户的登录名的用户。User based on a login based on a Windows Active Directory account. CREATE USER [Contoso\Fritz];
  • 基于 Windows 组的登录名的用户。User based on a login based on a Windows group. CREATE USER [Contoso\Sales];
  • 基于使用 SQL ServerSQL Server 身份验证的登录名的用户。User based on a login using SQL ServerSQL Server authentication. CREATE USER Mary;

在数据库中进行身份验证的用户 - 建议用来帮助提高数据库的可移植性。Users that authenticate at the database - Recommended to help make your database more portable.
始终可用于 SQL 数据库SQL DatabaseAlways allowed in SQL 数据库SQL Database. SQL ServerSQL Server 中,只能用于包含的数据库。Only allowed in a contained database in SQL ServerSQL Server.

  • 基于无登录名的 Windows 用户的用户。User based on a Windows user that has no login. CREATE USER [Contoso\Fritz];

  • 基于无登录名的 Windows 组的用户。User based on a Windows group that has no login. CREATE USER [Contoso\Sales];

  • 基于 Azure Active Directory 用户的 SQL 数据库SQL DatabaseSQL 数据仓库SQL Data Warehouse 中的用户。User in SQL 数据库SQL Database or SQL 数据仓库SQL Data Warehouse based on an Azure Active Directory user. CREATE USER [Fritz@contoso.com] FROM EXTERNAL PROVIDER;

  • 拥有密码的包含数据库用户。Contained database user with password. (不可用于 SQL 数据仓库SQL Data Warehouse。)CREATE USER Mary WITH PASSWORD = '********';(Not available in SQL 数据仓库SQL Data Warehouse.) CREATE USER Mary WITH PASSWORD = '********';

基于通过 Windows 组登录名连接的 Windows 主体的用户Users based on Windows principals that connect through Windows group logins

  • 基于无登录名但可通过 Windows 组中的成员身份连接到数据库引擎Database Engine的 Windows 用户的用户。User based on a Windows user that has no login, but can connect to the 数据库引擎Database Engine through membership in a Windows group. CREATE USER [Contoso\Fritz];

  • 基于无登录名但可通过其他 Windows 组中的成员身份连接到数据库引擎Database Engine的 Windows 组的用户。User based on a Windows group that has no login, but can connect to the 数据库引擎Database Engine through membership in a different Windows group. CREATE USER [Contoso\Fritz];

无法进行身份验证的用户 - 这些用户无法登录 SQL ServerSQL ServerSQL 数据库SQL DatabaseUsers that cannot authenticate - These users cannot login to SQL ServerSQL Server or SQL 数据库SQL Database.

  • 没有登录名的用户。User without a login. 不能登录,但可以被授予权限。Cannot login but can be granted permissions. CREATE USER CustomApp WITHOUT LOGIN;
  • 基于证书的用户。User based on a certificate. 不能登录,但可以被授予权限,也可以对模块进行签名。Cannot login but can be granted permissions and can sign modules. CREATE USER TestProcess FOR CERTIFICATE CarnationProduction50;
  • 基于非对称密钥的用户。User based on an asymmetric key. 不能登录,但可以被授予权限,也可以对模块进行签名。Cannot login but can be granted permissions and can sign modules. CREATE User TestProcess FROM ASYMMETRIC KEY PacificSales09;

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Database managed instance
  
-- Syntax Users based on logins in master  
CREATE USER user_name   
    [   
        { FOR | FROM } LOGIN login_name   
    ]  
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  
  
-- Users that authenticate at the database  
CREATE USER   
    {  
      windows_principal [ WITH <options_list> [ ,... ] ]  
  
    | user_name WITH PASSWORD = 'password' [ , <options_list> [ ,... ]   
    | Azure_Active_Directory_principal FROM EXTERNAL PROVIDER   
    }  
  
 [ ; ]  
  
-- Users based on Windows principals that connect through Windows group logins  
CREATE USER   
    {   
          windows_principal [ { FOR | FROM } LOGIN windows_principal ]  
        | user_name { FOR | FROM } LOGIN windows_principal  
}  
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  
  
-- Users that cannot authenticate   
CREATE USER user_name   
    {  
         WITHOUT LOGIN [ WITH <limited_options_list> [ ,... ] ]  
       | { FOR | FROM } CERTIFICATE cert_name   
       | { FOR | FROM } ASYMMETRIC KEY asym_key_name   
    }  
 [ ; ]  
  
<options_list> ::=  
      DEFAULT_SCHEMA = schema_name  
    | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }  
    | SID = sid   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]  
  
<limited_options_list> ::=  
      DEFAULT_SCHEMA = schema_name ]   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ]  
  
-- SQL Database syntax when connected to a federation member  
CREATE USER user_name  
[;]

-- Syntax for users based on Azure AD logins for Azure SQL Database managed instance
CREATE USER user_name   
    [   { FOR | FROM } LOGIN login_name  ]  
    | FROM EXTERNAL PROVIDER
    [ WITH <limited_options_list> [ ,... ] ]   
[ ; ]  

<limited_options_list> ::=  
      DEFAULT_SCHEMA = schema_name 
    | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }   
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ] ] 

重要

SQL 数据库托管实例的 Azure AD 登录名当前为公共预览版 。Azure AD logins for SQL Database managed instance is in public preview.

-- Syntax for Azure SQL Data Warehouse  
  
CREATE USER user_name   
    [ { { FOR | FROM } { LOGIN login_name }   
      | WITHOUT LOGIN  
    ]   
    [ WITH DEFAULT_SCHEMA = schema_name ]  
[;]

CREATE USER Azure_Active_Directory_principal FROM EXTERNAL PROVIDER  
    [ WITH DEFAULT_SCHEMA = schema_name ]  
[;]
-- Syntax for Parallel Data Warehouse  
  
CREATE USER user_name   
    [ { { FOR | FROM }  
      {   
        LOGIN login_name   
      }   
      | WITHOUT LOGIN  
    ]   
    [ WITH DEFAULT_SCHEMA = schema_name ]  
[;]  

参数Arguments

user_name user_name
指定在此数据库中用于识别该用户的名称。Specifies the name by which the user is identified inside this database. user_namesysnameuser_name is a sysname. 它的长度最多是 128 个字符。It can be up to 128 characters long. 在创建基于 Windows 主体的用户时,除非指定其他用户名,否则 Windows 主体名称将成为用户名。When creating a user based on a Windows principal, the Windows principal name becomes the user name unless another user name is specified.

LOGIN login_nameLOGIN login_name
指定要为其创建数据库用户的登录名。Specifies the login for which the database user is being created. login_name 必须是服务器中的有效登录名。login_name must be a valid login in the server. 可以是基于 Windows 主体(用户或组)的登录名,也可以是使用 SQL ServerSQL Server 身份验证的登录名。Can be a login based on a Windows principal (user or group), or a login using SQL ServerSQL Server authentication. 当此 SQL Server 登录名进入数据库时,它将获取正在创建的这个数据库用户的名称和 ID。When this SQL Server login enters the database, it acquires the name and ID of the database user that is being created. 在创建从 Windows 主体映射的登录名时,请使用格式 [<domainName>\<loginName>] 。When creating a login mapped from a Windows principal, use the format [<domainName>\<loginName>]. 有关示例,请参阅语法摘要For examples, see Syntax Summary.

如果 CREATE USER 语句是 SQL 批处理中唯一的语句,则 Azure SQL 数据库将支持 WITH LOGIN 子句。If the CREATE USER statement is the only statement in a SQL batch, Azure SQL Database supports the WITH LOGIN clause. 如果 CREATE USER 语句不是 SQL 批处理中唯一的语句或在动态 SQL 中执行,则不支持 WITH LOGIN 子句。If the CREATE USER statement is not the only statement in a SQL batch or is executed in dynamic SQL, the WITH LOGIN clause is not supported.

WITH DEFAULT_SCHEMA = schema_name WITH DEFAULT_SCHEMA = schema_name
指定服务器为此数据库用户解析对象名时将搜索的第一个架构。Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user.

'windows_principal''windows_principal'
指定正为其创建数据库用户的 Windows 主体。Specifies the Windows principal for which the database user is being created. windows_principal 可以是 Windows 用户或 Windows 组。The windows_principal can be a Windows user, or a Windows group. 即使 windows_principal 没有登录名,也会创建该用户。The user will be created even if the windows_principal does not have a login. 连接 SQL ServerSQL Server 时,如果 windows_principal 没有登录名,Windows 主体必须通过有登录名的 Windows 组中的成员身份在数据库引擎Database Engine中进行身份验证,或者连接字符串必须将包含的数据库指定为初始目录。When connecting to SQL ServerSQL Server, if the windows_principal does not have a login, the Windows principal must authenticate at the 数据库引擎Database Engine through membership in a Windows group that has a login, or the connection string must specify the contained database as the initial catalog. 在从 Windows 主体创建用户时,请使用格式 [<domainName>\<loginName>] 。When creating a user from a Windows principal, use the format [<domainName>\<loginName>]. 有关示例,请参阅语法摘要For examples, see Syntax Summary. 基于 Active Directory 用户的用户的名称限制为少于 21 个字符。Users based on Active Directory users, are limited to names of fewer than 21 characters.

'Azure_Active_Directory_principal''Azure_Active_Directory_principal'
适用范围:SQL 数据库SQL DatabaseSQL 数据仓库SQL Data WarehouseApplies to: SQL 数据库SQL Database, SQL 数据仓库SQL Data Warehouse.

指定正为其创建数据库用户的 Azure Active Directory 主体。Specifies the Azure Active Directory principal for which the database user is being created. Azure_Active_Directory_principal 可以是 Azure Active Directory 用户、Azure Active Directory 组或 Azure Active Directory 应用程序 。The Azure_Active_Directory_principal can be an Azure Active Directory user, an Azure Active Directory group, or an Azure Active Directory application. (Azure Active Directory 用户不能在 SQL 数据库SQL Database中拥有 Windows 身份验证登录名;只有数据库用户才能拥有。)连接字符串必须将包含的数据库指定为初始目录。(Azure Active Directory users cannot have Windows Authentication logins in SQL 数据库SQL Database; only database users.) The connection string must specify the contained database as the initial catalog.

对于 Azure AD 主体,CREATE USER 语法需要:For Azure AD principals, the CREATE USER syntax requires:

  • Azure AD 用户的 Azure AD 对象的 UserPrincipalName。UserPrincipalName of the Azure AD object for Azure AD Users.

    • CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
    • CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
  • Azure AD 组和 Azure AD 应用程序的 Azure AD 对象的 DisplayName。DisplayName of Azure AD object for Azure AD Groups and Azure AD Applications. 如果有 Nurses 安全组,可使用:If you had the Nurses security group, you would use:

    • CREATE USER [Nurses] FROM EXTERNAL PROVIDER;

有关详细信息,请参阅 使用 Azure Active Directory 身份验证连接到 SQL 数据库For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication.

WITH PASSWORD = 'password' WITH PASSWORD = 'password'
适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

只能在包含数据库中使用。Can only be used in a contained database. 为正在创建的用户指定密码。Specifies the password for the user that is being created. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,存储的密码信息使用 SHA-512 加盐密码进行计算。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

WITHOUT LOGINWITHOUT LOGIN
指定不应将用户映射到现有登录名。Specifies that the user should not be mapped to an existing login.

CERTIFICATE cert_name CERTIFICATE cert_name
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

指定要为其创建数据库用户的证书。Specifies the certificate for which the database user is being created.

ASYMMETRIC KEY asym_key_name ASYMMETRIC KEY asym_key_name
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

指定要为其创建数据库用户的非对称密钥。Specifies the asymmetric key for which the database user is being created.

DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }
适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

为新用户指定默认语言。Specifies the default language for the new user. 如果为用户指定了默认语言并在之后更改数据库的默认语言,则用户的默认语言仍会保留为指定的语言。If a default language is specified for the user and the default language of the database is later changed, the users default language remains as specified. 如果未指定默认语言,用户的默认语言将为数据库的默认语言。If no default language is specified, the default language for the user will be the default language of the database. 如果未指定用户的默认语言并在之后更改数据库的默认语言,用户的默认语言将更改为数据库的新默认语言。If the default language for the user is not specified and the default language of the database is later changed, the default language of the user will change to the new default language for the database.

重要

DEFAULT_LANGUAGE 仅用于包含的数据库用户。DEFAULT_LANGUAGE is used only for a contained database user.

SID = sid SID = sid
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

仅适用于包含的数据库中具有密码的用户(SQL ServerSQL Server 身份验证)。Applies only to users with passwords ( SQL ServerSQL Server authentication) in a contained database. 指定新数据库用户的 SID。Specifies the SID of the new database user. 如果未选择此选项,则 SQL ServerSQL Server 自动指派 SID。If this option is not selected, SQL ServerSQL Server automatically assigns a SID. 使用 SID 参数在具有同一标识 (SID) 的多个数据库中创建用户。Use the SID parameter to create users in multiple databases that have the same identity (SID). 当在多个数据库中创建用户以准备进行 Always On 故障转移时,这非常有用。This is useful when creating users in multiple databases to prepare for Always On failover. 若要确定用户的 SID,请查询 sys.database_principals。To determine the SID of a user, query sys.database_principals.

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]
适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

取消在大容量复制操作期间对服务器进行加密元数据检查。Suppresses cryptographic metadata checks on the server in bulk copy operations. 这使用户能够在表或数据库之间大容量复制加密数据,而无需对数据进行解密。This enables the user to bulk copy encrypted data between tables or databases, without decrypting the data. 默认为 OFF。The default is OFF.

警告

错误使用此选项可能导致数据损坏。Improper use of this option can lead to data corruption. 有关详细信息,请参阅迁移通过 Always Encrypted 保护的敏感数据For more information, see Migrate Sensitive Data Protected by Always Encrypted.

RemarksRemarks

如果已忽略 FOR LOGIN,则新的数据库用户将被映射到同名的 SQL ServerSQL Server 登录名。If FOR LOGIN is omitted, the new database user will be mapped to the SQL ServerSQL Server login with the same name.

默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. 除非另外指定,否则默认架构将是此数据库用户创建的对象所属的架构。Unless otherwise specified, the default schema will be the owner of objects created by this database user.

如果用户具有默认架构,则将使用默认架构。If the user has a default schema, that default schema will used. 如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。If the user does not have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. 如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。If the user does not have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. (不可能将可用的默认架构之一显式选作首选架构。)如果不能为用户确定默认架构,则将使用 dbo 架构 。(It is not possible to explicitly select one of the available default schemas as the preferred schema.) If no default schema can be determined for a user, the dbo schema will be used.

DEFAULT_SCHEMA 可在创建它所指向的架构前进行设置。DEFAULT_SCHEMA can be set before the schema that it points to is created.

在创建映射到证书或非对称密钥的用户时,不能指定 DEFAULT_SCHEMA。DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a certificate, or an asymmetric key.

如果用户是 sysadmin 固定服务器角色的成员,则忽略 DEFAULT_SCHEMA 的值。The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. sysadmin 固定服务器角色的所有成员都有默认架构 dboAll members of the sysadmin fixed server role have a default schema of dbo.

WITHOUT LOGIN 子句可创建不映射到 SQL Server 登录名的用户。The WITHOUT LOGIN clause creates a user that is not mapped to a SQL Server login. 它可以作为 guest 连接到其他数据库。It can connect to other databases as guest. 可以将权限分配给这一没有登录名的用户,当安全上下文更改为没有登录名的用户时,原始用户将收到无登录名用户的权限。Permissions can be assigned to this user without login and when the security context is changed to a user without login, the original users receives the permissions of the user without login. 请参阅示例 D. 创建和使用不含登录名的用户See example D. Creating and using a user without a login.

只有映射到 Windows 主体的用户才能包含反斜杠字符 ( \ )。Only users that are mapped to Windows principals can contain the backslash character (\).

不能使用 CREATE USER 创建 guest 用户,因为每个数据库中均已存在 guest 用户。CREATE USER cannot be used to create a guest user because the guest user already exists inside every database. 可通过授予 guest 用户 CONNECT 权限来启用该用户,如下所示:You can enable the guest user by granting it CONNECT permission, as shown:

GRANT CONNECT TO guest;  
GO  

可以在 sys.database_principals 目录视图中查看有关数据库用户的信息。Information about database users is visible in the sys.database_principals catalog view.

新的语法扩展 FROM EXTERNAL PROVIDER ,可用于在 SQL 数据库托管实例中创建服务器级别的 Azure AD 登录名。A new syntax extension, FROM EXTERNAL PROVIDER is available for creating server-level Azure AD logins in SQL Database managed instance. 使用 Azure AD 登录名,数据库级别的 Azure AD 主体能够映射到服务器级别的 Azure AD 登录名。Azure AD logins allow database-level Azure AD principals to be mapped to server-level Azure AD logins. 若要通过 Azure AD 登录名创建 Azure AD 用户,请使用以下语法:To create an Azure AD user from an Azure AD login use the following syntax:

CREATE USER [AAD_principal] FROM LOGIN [Azure AD login]

在 SQL 数据库托管实例中创建用户时,login_name 必须对应现有的 Azure AD 登录名,否则使用 FROM EXTERNAL PROVIDER 子句将只能在 master 数据库中创建没有登录名的 Azure AD 用户。When creating the user in the SQL Database managed instance database, the login_name must correspond to an existing Azure AD login, or else using the FROM EXTERNAL PROVIDER clause will only create an Azure AD user without a login in the master database. 例如,以下命令将创建容器用户:For example, this command will create a contained user:

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER

语法摘要Syntax Summary

基于 master 数据库中登录名的用户Users based on logins in master

下面的列表显示基于登录名的用户的可能语法。The following list shows possible syntax for users based on logins. 未列出默认架构选项。The default schema options are not listed.

  • CREATE USER [Domain1\WindowsUserBarry]
  • CREATE USER [Domain1\WindowsUserBarry] FOR LOGIN Domain1\WindowsUserBarry
  • CREATE USER [Domain1\WindowsUserBarry] FROM LOGIN Domain1\WindowsUserBarry
  • CREATE USER [Domain1\WindowsGroupManagers]
  • CREATE USER [Domain1\WindowsGroupManagers] FOR LOGIN [Domain1\WindowsGroupManagers]
  • CREATE USER [Domain1\WindowsGroupManagers] FROM LOGIN [Domain1\WindowsGroupManagers]
  • CREATE USER SQLAUTHLOGIN
  • CREATE USER SQLAUTHLOGIN FOR LOGIN SQLAUTHLOGIN
  • CREATE USER SQLAUTHLOGIN FROM LOGIN SQLAUTHLOGIN

在数据库中进行身份验证的用户Users that authenticate at the database

下面的列表显示只能在包含数据库中使用的用户的可能语法。The following list shows possible syntax for users that can only be used in a contained database. 创建的用户将不与 master 数据库中的任何登录名相关。The users created will not be related to any logins in the master database. 未列出默认架构和语言选项。The default schema and language options are not listed.

重要

此语法授予用户对数据库的访问权限,并且还将授予对数据库引擎Database Engine的新访问权限。This syntax grants users access to the database and also grants new access to the 数据库引擎Database Engine.

  • CREATE USER [Domain1\WindowsUserBarry]
  • CREATE USER [Domain1\WindowsGroupManagers]
  • CREATE USER Barry WITH PASSWORD = 'sdjklalie8rew8337!$d'

基于在 master 数据库中无登录名的 Windows 主体的用户Users based on Windows principals without logins in master

下面的列表显示可通过 Windows 组访问数据库引擎Database Engine但在 master 数据库中没有登录名的用户的可能语法。The following list shows possible syntax for users that have access to the 数据库引擎Database Engine through a Windows group but do not have a login in master. 此语法可用于所有类型的数据库中。This syntax can be used in all types of databases. 未列出默认架构和语言选项。The default schema and language options are not listed.

此语法与基于 master 数据库中登录名的用户相似,但此用户类别在 master 中没有登录名。This syntax is similar to users based on logins in master, but this category of user does not have a login in master. 该用户必须可以通过 Windows 组登录名访问数据库引擎Database EngineThe user must have access to the 数据库引擎Database Engine through a Windows group login.

此语法类似于基于 Windows 主体的包含数据库用户,但此用户类别未获得对数据库引擎Database Engine的新访问权限。This syntax is similar to contained database users based on Windows principals, but this category of user does not get new access to the 数据库引擎Database Engine.

  • CREATE USER [Domain1\WindowsUserBarry]
  • CREATE USER [Domain1\WindowsUserBarry] FOR LOGIN Domain1\WindowsUserBarry
  • CREATE USER [Domain1\WindowsUserBarry] FROM LOGIN Domain1\WindowsUserBarry
  • CREATE USER [Domain1\WindowsGroupManagers]
  • CREATE USER [Domain1\WindowsGroupManagers] FOR LOGIN [Domain1\WindowsGroupManagers]
  • CREATE USER [Domain1\WindowsGroupManagers] FROM LOGIN [Domain1\WindowsGroupManagers]

不能进行身份验证的用户Users that cannot authenticate

下面的列表显示无法登录 SQL ServerSQL Server 的用户的可能语法。The following list shows possible syntax for users that cannot login to SQL ServerSQL Server.

  • CREATE USER RIGHTSHOLDER WITHOUT LOGIN
  • CREATE USER CERTUSER FOR CERTIFICATE SpecialCert
  • CREATE USER CERTUSER FROM CERTIFICATE SpecialCert
  • CREATE USER KEYUSER FOR ASYMMETRIC KEY SecureKey
  • CREATE USER KEYUSER FROM ASYMMETRIC KEY SecureKey

SecuritySecurity

创建用户会授予对数据库的访问权限,但不会自动授予对数据库中对象的任何访问权限。Creating a user grants access to a database but does not automatically grant any access to the objects in a database. 创建用户后,常见操作是将用户添加到有权访问数据库对象的数据库角色中或向用户授予对象权限。After creating a user, common actions are to add users to database roles that have permission to access database objects, or grant object permissions to the user. 有关设计权限系统的信息,请参阅 Getting Started with Database Engine PermissionsFor information about designing a permissions system, see Getting Started with Database Engine Permissions.

包含数据库的特殊注意事项Special Considerations for Contained Databases

连接包含的数据库时,如果用户在 master 数据库中没有登录名,连接字符串必须包括包含的数据库名称作为初始目录。When connecting to a contained database, if the user does not have a login in the master database, the connection string must include the contained database name as the initial catalog. 拥有密码的包含数据库用户始终需要使用初始目录参数。The initial catalog parameter is always required for a contained database user with password.

在包含数据库中,创建用户有助于将数据库与数据库引擎Database Engine实例分离,以便可以轻松地将数据库移动到其他 SQL ServerSQL Server 实例中。In a contained database, creating users helps separate the database from the instance of the 数据库引擎Database Engine so that the database can easily be moved to another instance of SQL ServerSQL Server. 有关详细信息,请参阅包含的数据库包含的数据库用户 - 使你的数据库可移植For more information, see Contained Databases and Contained Database Users - Making Your Database Portable. 若要将数据库用户从基于 SQL ServerSQL Server 身份验证登录名的用户更改为拥有密码的包含数据库用户,请参阅 sp_migrate_user_to_contained (Transact-SQL)To change a database user from a user based on a SQL ServerSQL Server authentication login to a contained database user with password, see sp_migrate_user_to_contained (Transact-SQL).

在包含的数据库中,用户不必在 master 数据库中具有登录名。In a contained database, users do not have to have logins in the master database. 数据库引擎Database Engine管理员应该了解,可在数据库级别而非数据库引擎Database Engine级别授予对包含数据库的访问权限。administrators should understand that access to a contained database can be granted at the database level, instead of the 数据库引擎Database Engine level. 有关详细信息,请参阅 Security Best Practices with Contained DatabasesFor more information, see Security Best Practices with Contained Databases.

Azure SQL DatabaseAzure SQL Database 使用包含的数据库用户时,使用数据库级别防火墙规则(而不服务器级别防火墙规则)配置访问权限。When using contained database users on Azure SQL DatabaseAzure SQL Database, configure access using a database-level firewall rule, instead of a server-level firewall rule. 有关详细信息,请参阅 sp_set_database_firewall_rule(Azure SQL 数据库)For more information, see sp_set_database_firewall_rule (Azure SQL Database).

对于 SQL 数据库SQL DatabaseSQL 数据仓库SQL Data Warehouse的包含数据库用户,SSMS 可以支持多重身份验证。For SQL 数据库SQL Database and SQL 数据仓库SQL Data Warehouse contained database users, SSMS can support Multi-Factor Authentication. 有关详细信息,请参阅 SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse(SSMS 支持使用 SQL 数据库和 SQL 数据仓库的 Azure AD MFA)。For more information, see SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.

权限Permissions

需要对数据库具有 ALTER ANY USER 权限。Requires ALTER ANY USER permission on the database.

示例Examples

A.A. 基于 SQL Server 登录名创建数据库用户Creating a database user based on a SQL Server login

下面的示例首先创建一个名为 AbolrousHazemSQL ServerSQL Server 登录名,然后在 AbolrousHazem 中创建对应的数据库用户 AdventureWorks2012The following example first creates a SQL ServerSQL Server login named AbolrousHazem, and then creates a corresponding database user AbolrousHazem in AdventureWorks2012.

CREATE LOGIN AbolrousHazem   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  

更改为用户数据库。Change to a user database. 例如,在 SQL ServerSQL Server 中使用 USE AdventureWorks2012 语句。For example, in SQL ServerSQL Server use the USE AdventureWorks2012 statement. Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data Warehouse中,必须建立到用户数据库的新连接。In Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse, you must make a new connection to the user database.

CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;  
GO   

B.B. 创建具有默认架构的数据库用户Creating a database user with a default schema

下面的示例首先创建名为 WanidaBenshoof 且具有密码的服务器登录名,然后创建具有默认架构 Wanida 的对应数据库用户 MarketingThe following example first creates a server login named WanidaBenshoof with a password, and then creates a corresponding database user Wanida, with the default schema Marketing.

CREATE LOGIN WanidaBenshoof   
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';  
USE AdventureWorks2012;  
CREATE USER Wanida FOR LOGIN WanidaBenshoof   
    WITH DEFAULT_SCHEMA = Marketing;  
GO  

C.C. 从证书创建数据库用户Creating a database user from a certificate

下面的示例从证书 JinghaoLiu 创建数据库用户 CarnationProduction50The following example creates a database user JinghaoLiu from certificate CarnationProduction50.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

USE AdventureWorks2012;  
CREATE CERTIFICATE CarnationProduction50  
    WITH SUBJECT = 'Carnation Production Facility Supervisors',  
    EXPIRY_DATE = '11/11/2011';  
GO  
CREATE USER JinghaoLiu FOR CERTIFICATE CarnationProduction50;  
GO   

D.D. 创建和使用不含登录名的用户Creating and using a user without a login

以下示例创建一个数据库用户 CustomApp,该用户不映射到 SQL ServerSQL Server 登录名。The following example creates a database user CustomApp that does not map to a SQL ServerSQL Server login. 然后,该示例向用户 adventure-works\tengiz0 授予相应的权限以便模拟 CustomApp 用户。The example then grants a user adventure-works\tengiz0 permission to impersonate the CustomApp user.

USE AdventureWorks2012 ;  
CREATE USER CustomApp WITHOUT LOGIN ;  
GRANT IMPERSONATE ON USER::CustomApp TO [adventure-works\tengiz0] ;  
GO   

为了使用 CustomApp 凭据,用户 adventure-works\tengiz0 执行以下语句。To use the CustomApp credentials, the user adventure-works\tengiz0 executes the following statement.

EXECUTE AS USER = 'CustomApp' ;  
GO  

为了恢复到 adventure-works\tengiz0 凭据,该用户执行以下语句。To revert back to the adventure-works\tengiz0 credentials, the user executes the following statement.

REVERT ;  
GO  

E.E. 创建拥有密码的包含数据库用户Creating a contained database user with password

下面的示例创建一个拥有密码的包含数据库用户。The following example creates a contained database user with password. 该示例只能在包含数据库中执行。This example can only be executed in a contained database.

适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017. 如果删除了 DEFAULT_LANGUAGE,则此示例可在 SQL 数据库SQL Database 中正常运行。This example works in SQL 数据库SQL Database if DEFAULT_LANGUAGE is removed.

USE AdventureWorks2012 ;  
GO  
CREATE USER Carlo  
WITH PASSWORD='RN92piTCh%$!~3K9844 Bl*'  
    , DEFAULT_LANGUAGE=[Brazilian]  
    , DEFAULT_SCHEMA=[dbo]  
GO   

F.F. 为域登录名创建包含数据库用户Creating a contained database user for a domain login

下面的示例为 Contoso 域中名为 Fritz 的登录名创建一个包含数据库用户。The following example creates a contained database user for a login named Fritz in a domain named Contoso. 该示例只能在包含数据库中执行。This example can only be executed in a contained database.

适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

USE AdventureWorks2012 ;  
GO  
CREATE USER [Contoso\Fritz] ;  
GO   

G.G. 创建具有特定 SID 的包含数据库用户Creating a contained database user with a specific SID

下面的示例创建名为 CarmenW 的 SQL Server 经过身份验证的包含数据库用户。The following example creates a SQL Server authenticated contained database user named CarmenW. 该示例只能在包含数据库中执行。This example can only be executed in a contained database.

适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

USE AdventureWorks2012 ;  
GO  
CREATE USER CarmenW WITH PASSWORD = 'a8ea v*(Rd##+'  
, SID = 0x01050000000000090300000063FF0451A9E7664BA705B10E37DDC4B7;  
  

H.H. 创建用户以复制加密数据Creating a user to copy encrypted data

以下示例会创建一个用户,该用户可以将受 Always Encrypted 功能保护的数据从一组包含加密列的表复制到另一组具有加密列的表中(在相同或不同的数据库中)。The following example creates a user that can copy data that is protected by the Always Encrypted feature from one set of tables, containing encrypted columns, to another set of tables with encrypted columns (in the same or a different database). 有关详细信息,请参阅迁移通过 Always Encrypted 保护的敏感数据For more information, see Migrate Sensitive Data Protected by Always Encrypted.

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

CREATE USER [Chin]   
WITH   
      DEFAULT_SCHEMA = dbo  
    , ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = ON ;  

I.I. 在 SQL 数据库托管实例中通过 Azure AD 登录名创建 Azure AD 用户Create an Azure AD user from an Azure AD login in SQL Database managed instance

若要通过 Azure AD 登录名创建 Azure AD 用户,请使用以下语法。To create an Azure AD user from an Azure AD login, use the following syntax.

使用授予 sysadmin 角色的 Azure AD 登录名登录托管实例。Sign into your managed instance with an Azure AD login granted with the sysadmin role. 以下代码可通过登录名 bob@contoso.com 创建 Azure AD 用户 bob@contoso.com。The following creates an Azure AD user bob@contoso.com, from the login bob@contoso.com. 此登录名是在 CREATE LOGIN 示例中创建的。This login was created in the CREATE LOGIN example.

CREATE USER [bob@contoso.com] FROM LOGIN [bob@contoso.com];
GO

重要

通过 Azure AD 登录名创建 USER 时,请指定 user_name 作为 LOGIN 的相同 login_name 。When creating a USER from an Azure AD login, specify the user_name as the same login_name from LOGIN.

支持创建 Azure AD 用户,作为属于组的 Azure AD 登录名中的组。Creating an Azure AD user as a group from an Azure AD login that is a group is supported.

CREATE USER [AAD group] FROM LOGIN [AAD group];
GO

此外,还可通过属于组的 Azure AD 登录名创建 Azure AD 用户。You can also create an Azure AD user from an Azure AD login that is a group.

CREATE USER [bob@contoso.com] FROM LOGIN [AAD group];
GO

J.J. 为数据库创建没有 AAD 登录名的 Azure AD 用户Create an Azure AD user without an AAD login for the database

可使用以下语法在 SQL 数据库托管实例数据库(包含的用户)中创建 Azure AD 用户 bob@contoso.com:The following syntax is used to create an Azure AD user bob@contoso.com, in the SQL Database managed instance database (contained user):

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
GO

后续步骤Next steps

创建用户后,便可考虑使用 ALTER ROLE 语句将用户添加到某个数据库角色。Once the user is created, consider adding the user to a database role using the ALTER ROLE statement.
你可能还想对该角色 GRANT 对象权限,以便它能访问表。You might also want to GRANT Object Permissions to the role so they can access tables. 有关 SQL Server 安全模型的常规信息,请参阅权限For general information about the SQL Server security model, see Permissions.

另请参阅See Also

创建数据库用户 Create a Database User
sys.database_principals (Transact-SQL) sys.database_principals (Transact-SQL)
ALTER USER (Transact-SQL) ALTER USER (Transact-SQL)
DROP USER (Transact-SQL) DROP USER (Transact-SQL)
CREATE LOGIN (Transact-SQL) CREATE LOGIN (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
包含的数据库 Contained Databases
使用 Azure Active Directory 身份验证连接到 SQL 数据库 Connecting to SQL Database By Using Azure Active Directory Authentication
数据库引擎权限入门Getting Started with Database Engine Permissions