创建一个登录名Create a Login

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

本主题说明如何使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL 数据库SQL DatabaseSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中创建登录名。This topic describes how to create a login in SQL Server 2019 (15.x)SQL Server 2019 (15.x) or SQL 数据库SQL Database by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 登录名是连接 SQL ServerSQL Server实例的个人或进程的标识。A login is the identity of the person or process that is connecting to an instance of SQL ServerSQL Server.

背景Background:

登录名是一个可由安全系统进行身份验证的安全主体或实体。A login is a security principal, or an entity that can be authenticated by a secure system. 用户需要使用登录名连接到 SQL ServerSQL ServerUsers need a login to connect to SQL ServerSQL Server. 您可基于 Windows 主体(例如,域用户或 Windows 域组)创建登录名,或者也可创建一个并非基于 Windows 主体的登录名(例如, SQL ServerSQL Server 登录名)。You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL ServerSQL Server login).

注意: 若要使用 SQL ServerSQL Server 身份验证,数据库引擎Database Engine必须使用混合模式身份验证。 NOTE: To use Authentication, the must use mixed mode authentication. 有关详细信息,请参阅 选择身份验证模式For more information, see Choose an Authentication Mode

可以向作为安全主体的登录名授予权限。As a security principal, permissions can be granted to logins. 登录名的作用域是整个 数据库引擎Database EngineThe scope of a login is the whole 数据库引擎Database Engine. 若要连接 SQL ServerSQL Server实例上的特定数据库,登录名必须映射到数据库用户。To connect to a specific database on the instance of SQL ServerSQL Server, a login must be mapped to a database user. 数据库内的权限是向数据库用户而不是登录名授予和拒绝授予的。Permissions inside the database are granted and denied to the database user, not the login. 可将作用域为整个 SQL ServerSQL Server 实例的权限(例如 CREATE ENDPOINT 权限)授予一个登录名。Permissions that have the scope of the whole instance of SQL ServerSQL Server (for example, the CREATE ENDPOINT permission) can be granted to a login.

注意: 当登录名连接到 SQL ServerSQL Server 时,在 master 数据库验证标识。When a login connects to SQL ServerSQL Server the identity is validated at the master database. 使用包含的数据库用户在数据库级别对 SQL ServerSQL ServerSQL 数据库SQL Database 连接进行身份验证。Use contained database users to authenticate SQL ServerSQL Server and SQL 数据库SQL Database connections at the database level. 当使用包含的数据库用户时,登录名不是必需的。When using contained database users a login is not necessary. “包含的数据库”是独立于其他数据库以及承载数据库的 SQL ServerSQL Server/ SQL 数据库SQL Database 实例(和 master 数据库)的一种数据库。A contained database is a database that is isolated from other databases and from the instance of SQL ServerSQL Server/ SQL 数据库SQL Database (and the master database) that hosts the database. SQL ServerSQL Server 支持包含的数据库用户进行 Windows 和 SQL ServerSQL Server 身份验证。supports contained database users for both Windows and SQL ServerSQL Server authentication. 使用 SQL 数据库SQL Database时,将包含的数据库用户与数据库级别防火墙规则相结合。When using SQL 数据库SQL Database, combine contained database users with database level firewall rules. 有关详细信息,请参阅 包含的数据库用户 - 使你的数据库可移植For more information, see Contained Database Users - Making Your Database Portable.

SecuritySecurity

SQL ServerSQL Server 需要对服务器拥有 ALTER ANY LOGINALTER LOGIN 权限。 requires ALTER ANY LOGIN or ALTER LOGIN permission on the server.

SQL 数据库SQL Database 要求具有 loginmanager 角色的成员身份。 requires membership in the loginmanager role.

使用 SSMS 创建登录名

Create a login using SSMS

  1. 在对象资源管理器中,展开要在其中创建新登录名的服务器实例的文件夹。In Object Explorer, expand the folder of the server instance in which you want to create the new login.

  2. 右键单击“安全性”文件夹,指向“新建”,然后选择“登录名…” 。Right-click the Security folder, point to New, and select Login....

  3. 在“登录名 - 新建”对话框的“常规”页中,在“登录名”框中输入用户的名称 。In the Login - New dialog box, on the General page, enter the name of a user in the Login name box. 或者,单击“搜索…”以打开“选择用户或组”对话框 。Alternately, click Search... to open the Select User or Group dialog box.

    如果单击“搜索…” :If you click Search...:

    1. 在“选择此对象类型”下,单击“对象类型…”以打开“对象类型”对话框,并选择以下任意或全部选项: “内置安全主体”、“组”和“用户” 。Under Select this object type, click Object Types... to open the Object Types dialog box and select any or all of the following: Built-in security principals, Groups, and Users. 默认情况下,将选中“内置安全主体” 和“用户” 。 Built-in security principals and Users are selected by default. 完成后,单击 “确定”When finished, click OK.

    2. 在“从此位置”下,单击“位置…”以打开“位置”对话框,并选择一个可用的服务器位置 。Under From this location, click Locations... to open the Locations dialog box and select one of the available server locations. 完成后,单击 “确定”When finished, click OK.

    3. 在“输入要选择的对象名称(示例)” 下,输入你想要查找的用户或组名。Under Enter the object name to select (examples), enter the user or group name that you want to find. 有关详细信息,请参阅 “选择用户、计算机或组”对话框For more information, see Select Users, Computers, or Groups Dialog Box.

    4. 单击“高级…”以显示更多高级搜索选项 。Click Advanced... for more advanced search options. 有关详细信息,请参阅 选择“用户”、“计算机”或“组”对话框 - 高级页面For more information, see Select Users, Computers, or Groups Dialog Box - Advanced Page.

    5. 单击“确定”。 Click OK.

  4. 若要基于 Windows 主体创建一个登录名,请选择 “Windows 身份验证”To create a login based on a Windows principal, select Windows authentication. 这是默认选项。This is the default selection.

  5. 若要创建一个保存在 SQL ServerSQL Server 数据库中的登录名,请选择 “SQL Server 身份验证”To create a login that is saved on a SQL ServerSQL Server database, select SQL Server authentication.

    1. 在“密码”框中,输入新用户的密码。 In the Password box, enter a password for the new user. 在“确认密码”框中再次输入该密码。 Enter that password again into the Confirm Password box.

    2. 在更改现有密码时,选择 “指定旧密码” ,然后在 “旧密码” 框中键入旧密码。When changing an existing password, select Specify old password, and then type the old password in the Old password box.

    3. 若要强制实施有关复杂性和强制执行的密码策略选项,请选择 “强制实施密码策略”To enforce password policy options for complexity and enforcement, select Enforce password policy. 有关详细信息,请参阅 Password PolicyFor more information, see Password Policy. 选中 “SQL Server 身份验证” 时,这是默认选项。This is a default option when SQL Server authentication is selected.

    4. 若要强制实施有关过期的密码策略选项,请选中 “强制密码过期”To enforce password policy options for expiration, select Enforce password expiration. 必须选择 “强制实施密码策略” 才能启用此复选框。Enforce password policy must be selected to enable this checkbox. 选中 “SQL Server 身份验证” 时,这是默认选项。This is a default option when SQL Server authentication is selected.

    5. 若要在首次使用登录名后强制用户创建新密码,请选择 “用户在下次登录时必须更改密码”To force the user to create a new password after the first time the login is used, select User must change password at next login. 必须选择 “强制密码过期” 才能启用此复选框。Enforce password expiration must be selected to enable this checkbox. 选中 “SQL Server 身份验证” 时,这是默认选项。This is a default option when SQL Server authentication is selected.

  6. 若要将登录名与独立的安全性证书相关联,请选择“映射到证书” ,然后再从列表中选择现有证书的名称。To associate the login with a stand-alone security certificate, select Mapped to certificate and then select the name of an existing certificate from the list.

  7. 若要将登录名与独立的非对称密钥相关联,请选择“映射到非对称密钥” ,然后再从列表中选择现有密钥的名称。To associate the login with a stand-alone asymmetric key, select Mapped to asymmetric key to, and then select the name of an existing key from the list.

  8. 若要将登录名与安全凭据相关联,请选中 “映射到凭据” 复选框,然后再从列表中选择现有凭据或单击 “添加” 以创建新的凭据。To associate the login with a security credential, select the Mapped to Credential check box, and then either select an existing credential from the list or click Add to create a new credential. 若要从登录名删除与某个安全凭据的映射,请从 “映射的凭据” 中选择该凭据,然后单击 “删除”To remove a mapping to a security credential from the login, select the credential from Mapped Credentials and click Remove. 有关常规凭据的详细信息,请参阅凭据(数据库引擎)For more information about credentials in general, see Credentials (Database Engine).

  9. “默认数据库” 列表中,选择登录名的默认数据库。From the Default database list, select a default database for the login. “Master” 是此选项的默认值。 master is the default for this option.

  10. “默认语言” 列表中,选择登录名的默认语言。From the Default language list, select a default language for the login.

  11. 单击“确定”。 Click OK.

其他选项Additional Options

“登录名 - 新建”对话框中还提供了其他四个页面上选项: “服务器角色”、“用户映射”、“安全对象”和“状态” 。The Login – New dialog box also offers options on four additional pages: Server Roles, User Mapping, Securables, and Status.

“服务器角色”Server Roles

“服务器角色” 页将列出可分配给新登录名的所有可能的角色。The Server Roles page lists all possible roles that can be assigned to the new login. 提供了以下选项:The following options are available:

“bulkadmin” 复选框 bulkadmin check box
bulkadmin 固定服务器角色的成员可以运行 BULK INSERT 语句。Members of the bulkadmin fixed server role can run the BULK INSERT statement.

“dbcreator” 复选框 dbcreator check box
dbcreator 固定服务器角色的成员可以创建、更改、删除和还原任何数据库。Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

“diskadmin” 复选框 diskadmin check box
diskadmin 固定服务器角色的成员可以管理磁盘文件。Members of the diskadmin fixed server role can manage disk files.

“processadmin” 复选框 processadmin check box
processadmin 固定服务器角色的成员可以终止在 数据库引擎Database Engine实例中运行的进程。Members of the processadmin fixed server role can terminate processes running in an instance of the 数据库引擎Database Engine.

“public” 复选框 public check box
默认情况下,所有 SQL Server 用户、组和角色都属于 public 固定服务器角色。All SQL Server users, groups, and roles belong to the public fixed server role by default.

“securityadmin” 复选框 securityadmin check box
securityadmin 固定服务器角色的成员可以管理登录名及其属性。Members of the securityadmin fixed server role manage logins and their properties. 他们可以 GRANT、DENY 和 REVOKE 服务器级别的权限。They can GRANT, DENY, and REVOKE server-level permissions. 他们还可以 GRANT、DENY 和 REVOKE 数据库级别的权限。They can also GRANT, DENY, and REVOKE database-level permissions. 此外,他们还可以重置 SQL ServerSQL Server 登录名的密码。Additionally, they can reset passwords for SQL ServerSQL Server logins.

“serveradmin” 复选框 serveradmin check box
serveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

“setupadmin” 复选框 setupadmin check box
setupadmin 固定服务器角色成员可以添加和删除链接服务器,并可以执行某些系统存储过程。Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.

“sysadmin” 复选框 sysadmin check box
sysadmin 固定服务器角色的成员可以在 数据库引擎Database Engine中执行任何活动。Members of the sysadmin fixed server role can perform any activity in the 数据库引擎Database Engine.

用户映射User Mapping

“用户映射” 页将列出可应用于登录名的所有可能的数据库以及这些数据库上的数据库角色成员身份。The User Mapping page lists all possible databases and the database role memberships on those databases that can be applied to the login. 选定的数据库将确定对登录名可用的角色成员身份。The databases selected determine the role memberships that are available for the login. 此页还将提供以下选项:The following options are available on this page:

映射到此登录名的用户 Users mapped to this login
选择此登录名可以访问的数据库。Select the databases that this login can access. 选择某个数据库时,其有效的数据库角色将会显示在“数据库角色成员身份: database_name” 窗格中。When you select a database, its valid database roles are displayed in the Database role membership for: database_name pane.

Map Map:
允许登录名访问下面列出的数据库。Allow the login to access the databases listed below.

Databasedatabase
列出服务器上可用的数据库。Lists the databases available on the server.

用户user
指定要映射到登录名的数据库用户。Specify a database user to map to the login. 默认情况下,数据库用户名与登录名相同。By default, the database user has the same name as the login.

默认架构 Default Schema.
指定用户的默认架构。Specifies the default schema of the user. 首次创建用户时,其默认架构是 dboWhen a user is first created, its default schema is dbo. 可以指定并不存在的默认架构。It is possible to specify a default schema that does not yet exist. 对于已映射到 Windows 组、证书或非对称密钥的用户,无法为其指定默认架构。You cannot specify a default schema for a user that is mapped to a Windows group, a certificate, or an asymmetric key.

已启用 Guest 帐户: database_name Guest account enabled for: database_name
只读属性,指示所选数据库是否已启用 Guest 帐户。Read-only attribute indicating whether the Guest account is enabled on the selected database. 可使用 Guest 帐户的 “登录属性” 对话框的 “状态” 页来启用或禁用 Guest 帐户。Use the Status page of the Login Properties dialog box of the Guest account to enable or disable the Guest account.

数据库角色成员身份: database_name Database role membership for: database_name
选择用户在指定数据库中的角色。Select the roles for the user in the specified database. 在每个数据库中,所有用户都是 public 角色的成员,并且不能被删除。All users are members of the public role in every database and cannot be removed. 有关数据库角色的详细信息,请参阅 数据库级别的角色For more information about database roles, see Database-Level Roles.

安全对象Securables

“安全对象” 页将列出所有可能的安全对象以及可授予登录名的针对这些安全对象的权限。The Securables page lists all possible securables and the permissions on those securables that can be granted to the login. 此页还将提供以下选项:The following options are available on this page:

上部网格 Upper Grid
包含一个或多个可以为其设置权限的项目。Contains one or more items for which permissions can be set. 上部网格中显示的列会根据主体或安全对象的不同而变化。The columns that are displayed in the upper grid vary depending on the principal or securable.

向上部网格中添加项目:To add items to the upper grid:

  1. 单击 “搜索”Click Search.

  2. 在“添加对象”对话框中,选择以下选项之一: “特定对象...”、“所有类型的对象...”或“服务器 server_name” 。In the Add Objects dialog box, select one of the following options: Specific objects…, All objects of the types…, or The serverserver_name. 单击“确定”。 Click OK.

    注意: 如果选择“服务器 server_name”,将使用该服务器的所有安全对象自动填充上部网格 。 NOTE: Selecting The server servername automatically fills the upper grid with all of that servers' securable objects.

  3. 如果选择“特定对象…”: If you select Specific objects...:

    1. 在“选择对象”对话框中的“选择这些对象类型”下,单击“对象类型…” 。In the Select Objects dialog box, under Select these object types, click Object Types....

    2. 在“选择对象类型”对话框中,选择以下任意或全部对象类型: “端点”、“登录名”、“服务器”、“可用性组”和“服务器角色” 。In the Select Object Types dialog box, select any or all of the following object types: Endpoints, Logins, Servers, Availability Groups, and Server roles. 单击“确定”。 Click OK.

    3. 在“输入要选择的对象名称(示例)”下,单击“浏览…” 。Under Enter the object names to select (examples), click Browse....

    4. “查找对象” 对话框中,选择您在 “选择对象类型” 对话框中选择的类型的任何可用对象,然后单击 “确定”In the Browse for Objects dialog box, select any of the available objects of the type that you selected in the Select Object Types dialog box, and then click OK.

    5. “选择对象” 对话框中,单击 “确定”In the Select Objects dialog box, click OK.

  4. 如果选择“所有类型的对象…”,请在“选择对象类型”对话框中,选择以下任意或全部对象类型: “端点”、“登录名”、“服务器”、“可用性组”和“服务器角色” 。If you select All objects of the types..., in the Select Object Types dialog box, select any or all of the following object types: Endpoints, Logins, Servers, Availability Groups, and Server roles. 单击“确定”。 Click OK.

名称Name
添加到网格中的每个主体或安全对象的名称。The name of each principal or securable that is added to the grid.

类型 type
描述每个项目的类型。Describes the type of each item.

“显式”选项卡 Explicit Tab
列出了上部网格中选定的安全对象的可能权限。Lists the possible permissions for the securable that are selected in the upper grid. 并非所有选项均用于任何显式权限。Not all options are available for all explicit permissions.

权限Permissions
权限的名称。The name of the permission.

授权者GRANTOR
授予该权限的主体。The principal that granted the permission.

授予Grant
选中该选项可以将此权限授予该登录名。Select to grant this permission to the login. 清除该选项将撤消此权限。Clear to revoke this permission.

具有授予权限 With Grant
反映所列权限的 WITH GRANT 选项的状态。Reflects the state of the WITH GRANT option for the listed permission. 此框是只读的。This box is read-only. 若要应用此权限,请使用 GRANT 语句。To apply this permission, use the GRANT statement.

拒绝 Deny:
选中该选项可以拒绝该登录名具有该权限。Select to deny this permission to the login. 清除该选项将撤消此权限。Clear to revoke this permission.

状态Status

“状态” 页将列出可对选定的 SQL ServerSQL Server 登录名配置的一些身份验证和授权选项。The Status page lists some of the authentication and authorization options that can be configured on the selected SQL ServerSQL Server login.

此页还将提供以下选项:The following options are available on this page:

连接到数据库引擎的权限 Permission to connect to database engine
当使用此设置时,应将所选登录名视为可授予或拒绝授予其对安全对象的访问权限的主体。When you work with this setting, you should think of the selected login as a principal that can be granted or denied permission on a securable.

如果选择 “授予” ,将向登录名授予 CONNECT SQL 权限。Select Grant to grant CONNECT SQL permission to the login. 如果选择 “拒绝” ,将拒绝向登录名授予 CONNECT SQL 权限。Select Deny to deny CONNECT SQL to the login.

登录login
当使用此设置时,应将所选登录名视为表中的记录。When you work with this setting, you should think of the selected login as a record in a table. 对此处列出的值的更改将应用于该记录。Changes to the values listed here will be applied to the record.

已禁用的登录名继续作为记录存在。A login that has been disabled continues to exist as a record. 但是如果它尝试连接到 SQL ServerSQL Server,则登录名将不能通过身份验证。But if it tries to connect to SQL ServerSQL Server, the login will not be authenticated.

选择此选项以启用或禁用此登录名。Select this option to enable or disable this login. 此选项将 ALTER LOGIN 语句与 ENABLE 或者 DISABLE 选项配合使用。This option uses the ALTER LOGIN statement with the either ENABLE or DISABLE option.

SQL Server 身份验证 SQL Server authentication:
仅当所选的登录名使用 SQL ServerSQL Server 身份验证进行连接并且登录名已锁定时,复选框“登录名已锁定” 才可用。该设置是只读的。The check box Login is locked out is only available if the selected login connects using SQL ServerSQL Server Authentication and the login has been locked out. 若要解除对已锁定登录名的锁定,请执行带 UNLOCK 选项的 ALTER LOGIN。To unlock a login that is locked out, execute ALTER LOGIN with the UNLOCK option.

使用 T-SQL 创建使用 Windows 身份验证的登录名

Create a login using Windows Authentication using T-SQL

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    -- Create a login for SQL Server by specifying a server name and a Windows domain account name.  
    
    CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;  
    GO  
    
    

使用 T-SQL 创建使用 SQL Server 身份验证的登录名Create a login using Windows Authentication using T-SQL

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    -- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty"   
    -- The user login starts with the password "Baz1nga," but that password must be changed after the first login.  
    
    CREATE LOGIN shcooper   
       WITH PASSWORD = 'Baz1nga' MUST_CHANGE,  
       CREDENTIAL = RestrictedFaculty;  
    GO  
    

有关详细信息,请参阅 CREATE LOGIN (Transact-SQL)For more information, see CREATE LOGIN (Transact-SQL).

跟进:在创建登录名后采取的步骤

Follow Up: Steps to take after you create a login
创建登录名后,该登录名可连接到 SQL ServerSQL Server,但不一定有执行任何有用工作的充分权限。After creating a login, the login can connect to SQL ServerSQL Server, but does not necessarily have sufficient permission to perform any useful work. 下面的列表提供了指向常见登录操作的链接。The following list provides links to common login actions.

另请参阅See Also

SQL Server 数据库引擎和 Azure SQL Database 的安全中心 Security Center for SQL Server Database Engine and Azure SQL Database