win 创建mysql用户_创建数据库用户 - SQL Server | Microsoft Docs

创建数据库用户Create a Database User

04/24/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse

本主题介绍如何创建最常见类型的数据库用户。This topic describes how to create the most common types of database users. 有十一种类型的用户。There are eleven types of users. The complete list is provided in the topic CREATE USER (Transact-SQL). 所有类型的 SQL ServerSQL Server 都支持数据库用户，但不一定支持所有类型的用户。All varieties of SQL ServerSQL Server support database users, but not necessarily all types of users.

可以通过使用 SQL Server Management StudioSQL Server Management Studio 或使用 Transact-SQLTransact-SQL来创建数据库用户。You can create a database user by using SQL Server Management StudioSQL Server Management Studio or by using Transact-SQLTransact-SQL.

了解用户类型Understanding the Types of Users

Management StudioManagement Studio 提供了创建数据库用户时的 6 个选项。presents 6 options when creating a database user. 下图在绿框中显示了这 6 个选项，并展示了选项所代表的含义。The following graphic shows the 6 options in the green box, and indicates what they represent.

选择用户类型Selecting the Type of User

登录名或没有映射到登录名的用户Login or user that is not mapped to a login

如果对 SQL ServerSQL Server不熟悉，可能很难决定要创建哪种类型的用户。If you are new to SQL ServerSQL Server, it can be difficult to determine what type of user you want to create. 首先问问自己，需要访问数据库的用户或组是否有登录名？First ask yourself, does the person or group that needs to access the database have a login? 管理 SQL ServerSQL Server 的用户和需要访问 SQL ServerSQL Server实例上的多个或者全部数据库的用户通常拥有主数据库中的登录名。Logins in the master database are common for the people who manage the SQL ServerSQL Server and for people who need to access many or all of the database on the instance of SQL ServerSQL Server. 在这种情况下，你需要创建一个“带登录名的 SQL 用户” 。For this situation, you will create a SQL user with login. 数据库用户是连接到数据库时的登录名的标识。The database user is the identity of the login when it is connected to a database. 数据库用户可以使用与登录名相同的名称，但这不是必需的。The database user can use the same name as the login, but that is not required. 本主题假设 SQL ServerSQL Server中已存在登录名。This topic assumes that a login already exists in SQL ServerSQL Server. 有关如何创建登录名的信息，请参阅 创建登录名For information about how to create a login, see Create a Login

如果需要访问数据库的用户和组没有登录名，并且他们只需要访问一个或少数几个数据库，则创建 Windows 用户 或者 带密码的 SQL 用户。If the person or group that needs to access the database does not have a login and if they only need access to one or few databases, create a Windows user or a SQL user with password. 也称为包含的数据库用户，它与主数据库的登录名不相关。Also called a contained database user, it is not associated with a login in the master database. 当你想在 SQL ServerSQL Server的实例间轻松地移动数据库时，这是一个理想的选择。This is an excellent choice when you want to be able to easily move your database between instances of SQL ServerSQL Server. 若要对 SQL Server 2016 (13.x)SQL Server 2016 (13.x)使用此选项，管理员必须首先对 SQL ServerSQL Server启用包含的数据库，然后对包含启用数据库。To use this option on SQL Server 2016 (13.x)SQL Server 2016 (13.x), an administrator must first enable contained databases for the SQL ServerSQL Server, and the database be enabled for containment.

重要说明！IMPORTANT! 作为包含的数据库用户进行连接时，必须在连接字符串中提供数据库的名称。When connecting as a contained database user you must provide the name of the database as part of the connection string. 若要在 Management StudioManagement Studio中指定数据库，在“连接到” 对话框中单击“选项” ，然后单击“连接属性” 选项卡。To specify the database in Management StudioManagement Studio, in the Connect to dialog box, click Options, and then click the Connection Properties tab.

当用户连接无法使用 Windows 进行身份验证时，选择“带密码的 SQL 用户” 或者“带用户名的 SQL 用户” ，具体取决于 SQL Server 身份验证登录名。Select SQL user with password or a SQL user with login based on a SQL Server authentication login, when the person connecting cannot authenticate with Windows. 组织外的用户(例如客户)连接到你的 SQL ServerSQL Server时这种情况很常见。This is common when people outside of your organization (for example customers) are connecting to your SQL ServerSQL Server.

提示！TIP! 对于组织内的用户，最好选择使用 Windows 身份验证。因为组织内的用户不需要记住其他密码，而且 Windows 身份验证可以提供其他安全功能，例如 Kerberos。For people inside your organization, Windows authentication is a better choice, because they won't have to remember an additional password, and because Windows authentication offers additional security features such as Kerberos.

背景Background

用户是数据库级别安全主体。A user is a database level security principal. 登录名必须映射到数据库用户才能连接到数据库。Logins must be mapped to a database user to connect to a database. 一个登录名可以作为不同用户映射到不同的数据库，但在每个数据库中只能作为一个用户进行映射。A login can be mapped to different databases as different users but can only be mapped as one user in each database. 在部分包含数据库中，可以创建不具有登录名的用户。In a partially contained database, a user can be created that does not have a login. For more information about contained database users, see CREATE USER (Transact-SQL). 如果在数据库中启用了 guest 用户，未映射到数据库用户的登录名可作为 guest 用户进入该数据库。If the guest user in a database is enabled, a login that is not mapped to a database user can enter the database as the guest user.

重要说明！IMPORTANT! guest 用户通常处于禁用状态。The guest user is ordinarily disabled. 除非有必要，否则不要启用 guest 用户。Do not enable the guest user unless it is necessary.

可以向作为安全主体的用户授予权限。As a security principal, permissions can be granted to users. 用户的作用域是数据库。The scope of a user is the database. 若要连接 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.

权限Permissions

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

使用 SSMS 创建用户Create a user with SSMS

在对象资源管理器中，展开 “数据库” 文件夹。In Object Explorer, expand the Databases folder.

展开要在其中创建新数据库用户的数据库。Expand the database in which to create the new database user.

右键单击“安全性”文件夹，指向“新建”，然后选择“用户…” 。Right-click the Security folder, point to New, and select User....

在“常规”页上的“数据库用户 - 新建”对话框中，从“用户类型”列表中选择以下一个用户类型 ：In the Database User - New dialog box, on the General page, select one of the following user types from the User type list:

带登录名的 SQL 用户SQL user with login

带密码的 SQL 用户SQL user with password

不带登录名的 SQL 用户SQL user without login

映射到证书的用户User mapped to a certificate

映射到非对称密钥的用户User mapped to an asymmetric key

Windows 用户Windows user

选择选项时，对话框中的其他选项可能改变。When you select an option, the remaining options in the dialog may change. 某些选项仅适用于特定类型的数据库用户。Some options only apply to specific types of database users. 某些选项可以为空，并且将使用默认值。Some options can be left blank and will use a default value.

用户名User name

输入新用户的名称。Enter a name for the new user. 如果你从“用户类型”列表中选择了“Windows 用户”，则还可以单击省略号 (…) 打开“选择用户或组”对话框 。If you have chosen Windows user from the User type list, you can also click the ellipsis (...) to open the Select User or Group dialog box.

登录名Login name

输入用户的登录名。Enter the login for the user. 或者，单击省略号 (…) 以打开“选择登录名”对话框 。Alternately, click the ellipsis (...) to open the Select Login dialog box. 如果您从 “用户类型” 列表中选择了 “带登录名的 SQL 用户” 或 “Windows 用户” ，则 “登录名” 可用。Login name is available if you select either SQL user with login or Windows user from the User type list.

“密码” 和 “确认密码”Password and Confirm password

输入在数据库中进行身份验证的用户的密码。Enter a password for users who authenticate at the database.

默认语言Default language

输入默认的用户语言。Enter the default language of the user.

默认架构Default schema

输入此用户所创建的对象所属的架构。Enter the schema that will own objects created by this user. 或者，单击省略号 (…) 以打开“选择架构”对话框 。Alternately, click the ellipsis (...) to open the Select Schema dialog box. 如果您从 “用户类型” 列表中选择了 “带登录名的 SQL 用户” , “不带登录名的 SQL 用户” 或 “Windows 用户” ，则 “默认架构” 可用。Default schema is available if you select either SQL user with login, SQL user without login, or Windows user from the User type list.

证书名称Certificate name

输入将用于数据库用户的证书。Enter the certificate to be used for the database user. 或者，单击省略号 (…) 以打开“选择证书”对话框 。Alternately, click the ellipsis (...) to open the Select Certificate dialog box. 如果从 “用户类型” 列表中选择了 “映射到证书的用户” ，则 “证书名称” 可用。Certificate name is available if you select User mapped to a certificate from the User type list.

非对称密钥名称Asymmetric key name

输入将用于数据库用户的密钥。Enter the key to be used for the database user. 或者，单击省略号 (…) 以打开“选择非对称密钥”对话框 。Alternately, click the ellipsis (...) to open the Select Asymmetric Key dialog box. 如果从 “用户类型” 列表中选择了 “映射到非对称密钥的用户” ，则 “非对称密钥名称” 可用。Asymmetric key name is available if you select User mapped to an asymmetric key from the User type list.

单击“确定”。Click OK.

其他选项Additional Options

“数据库用户 - 新建”对话框还提供了四个其他页上的选项：“拥有的架构”、“成员身份”、“安全对象”和“扩展属性” 。The Database User - New dialog box also offers options on four additional pages: Owned Schemas, Membership, Securables, and Extended Properties.

“拥有的架构” 页列出了可由新的数据库用户拥有的所有可能的架构。The Owned Schemas page lists all possible schemas that can be owned by the new database user. 若要向数据库用户添加架构或者从数据库用户中删除架构，请在 “此用户拥有的架构” 下选中或取消选中架构旁边的复选框。To add schemas to or remove them from a database user, under Schemas owned by this user, select or clear the check boxes next to the schemas.

“成员身份” 页列出了可由新的数据库用户拥有的所有可能的数据库成员身份角色。The Membership page lists all possible database membership roles that can be owned by the new database user. 若要向数据库用户添加角色或者从数据库用户中删除角色，请在 “数据库角色成员身份” 下选中或取消选中角色旁边的复选框。To add roles to or remove them from a database user, under Database role membership, select or clear the check boxes next to the roles.

“安全对象” 页将列出所有可能的安全对象以及可授予登录名的针对这些安全对象的权限。The Securables page lists all possible securables and the permissions on those securables that can be granted to the login.

“扩展属性” 页允许您向数据库用户添加自定义属性。The Extended properties page allows you to add custom properties to database users. 此页还提供以下选项：The following options are available on this page.

DatabaseDatabase

显示所选数据库的名称。Displays the name of the selected database. 此字段为只读。This field is read-only.

排序规则Collation

显示用于所选数据库的排序规则。Displays the collation used for the selected database. 此字段为只读。This field is read-only.

属性Properties

查看或指定对象的扩展属性。View or specify the extended properties for the object. 每个扩展属性都由与该对象关联的元数据的名称/值对组成。Each extended property consists of a name/value pair of metadata associated with the object.

省略号 (...)Ellipsis (...)

单击“值”后面的省略号 (…) 按钮可打开“已扩展属性的值”对话框 。Click the ellipsis (...) after Value to open the Value for Extended Property dialog box. 在这一较大的范围中键入或查看扩展属性的值。Type or view the value of the extended property in this larger location. 有关详细信息，请参阅 “扩展属性的值”对话框。

删除Delete

删除所选扩展属性。Removes the selected extended property.

使用 T-SQL 创建用户Create a user using T-SQL

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

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

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

-- Creates the login AbolrousHazem with password '340$Uuxwp7Mcxo7Khy'.

CREATE LOGIN AbolrousHazem

WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

GO

-- Creates a database user for the login created above.

CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;

GO

有关详细信息，请参阅 CREATE USER (Transact-SQL&)，其中包含更多的 Transact-SQLTransact-SQL 示例。For more information, see CREATE USER (Transact-SQL) which contains many more Transact-SQLTransact-SQL examples.

另请参阅See Also