sp_adduser (Transact-SQL)sp_adduser (Transact-SQL)

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

向当前数据库中添加新的用户。Adds a new user to the current database.

重要

此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 使用CREATE USER相反。Use CREATE USER instead.

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

语法Syntax

  
sp_adduser [ @loginame = ] 'login'   
    [ , [ @name_in_db = ] 'user' ]   
    [ , [ @grpname = ] 'role' ]   

参数Arguments

[ @loginame = ] 'login' 是的名称SQL ServerSQL Server登录名或 Windows 登录名。[ @loginame = ] 'login' Is the name of the SQL ServerSQL Server login or Windows login. 登录名sysname,无默认值。login is a sysname, with no default. 登录名必须是现有SQL ServerSQL Server登录名或 Windows 登录名。login must be an existing SQL ServerSQL Server login or Windows login.

[ @name_in_db = ] 'user' 是新的数据库用户的名称。[ @name_in_db = ] 'user' Is the name for the new database user. 用户sysname,默认值为 NULL。user is a sysname, with a default of NULL. 如果用户未指定,则新的数据库用户的名称默认为登录名名称。If user is not specified, the name of the new database user defaults to the login name. 指定用户为新用户提供不同的服务器级别登录名从数据库中的名称。Specifying user gives the new user a name in the database different from the server-level login name.

[ @grpname = ] 'role' 是的新用户成为其成员的数据库角色。[ @grpname = ] 'role' Is the database role of which the new user becomes a member. 角色sysname ,默认值为 NULL。role is sysname, with a default of NULL. 角色必须是当前数据库中的有效的数据库角色。role must be a valid database role in the current database.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

sp_adduser还将创建一个架构具有的用户的名称。sp_adduser will also create a schema that has the name of the user.

在添加完用户之后,可以使用 GRANT、DENY 和 REVOKE 等语句来定义控制用户所执行的活动的权限。After a user has been added, use the GRANT, DENY, and REVOKE statements to define the permissions that control the activities performed by the user.

使用sys.server_principals以显示有效登录名的列表。Use sys.server_principals to display a list of valid login names.

使用sp_helprole显示有效的角色名称的列表。Use sp_helprole to display a list of the valid role names. 当指定一个角色时,用户会自动地获得那些为该角色定义的权限。When you specify a role, the user automatically gains the permissions that are defined for the role. 如果不指定角色,用户将获得默认授予的权限公共角色。If a role is not specified, the user gains the permissions granted to the default public role. 若要将用户添加到角色,为值用户名必须提供。To add a user to a role, a value for the user name must be supplied. (用户名可以是与相同login_id。)(username can be the same as login_id.)

用户来宾每个数据库中已存在。User guest already exists in every database. 添加用户来宾将启用此用户,如果之前处于禁用状态。Adding user guest will enable this user, if it was previously disabled. 默认情况下,用户来宾在新数据库中禁用。By default, user guest is disabled in new databases.

sp_adduser不能在用户定义的事务内执行。sp_adduser cannot be executed inside a user-defined transaction.

无法添加来宾用户由于来宾每个数据库中已存在的用户。You cannot add a guest user because a guest user already exists inside every database. 若要启用来宾用户,授予来宾CONNECT 权限所示:To enable the guest user, grant guest CONNECT permission as shown:

GRANT CONNECT TO guest;  
GO  

权限Permissions

要求具有数据库的所有权。Requires ownership of the database.

示例Examples

A.A. 添加数据库用户Adding a database user

以下示例使用现有的 SQL ServerSQL Server 登录名 Vidur,将数据库用户 Recruiting 添加到当前数据库中的现有 Vidur 角色。The following example adds the database user Vidur to the existing Recruiting role in the current database, using the existing SQL ServerSQL Server login Vidur.

EXEC sp_adduser 'Vidur', 'Vidur', 'Recruiting';  

B.B. 添加数据库用户(使用相同的登录 ID)Adding a database user with the same login ID

以下示例将用户 Arvind 添加到 SQL ServerSQL Server 登录名 Arvind 的当前数据库。The following example adds user Arvind to the current database for the SQL ServerSQL Server login Arvind. 此用户归属的默认值公共角色。This user belongs to the default public role.

EXEC sp_adduser 'Arvind';  

C.C. 添加数据库用户(使用不同于其服务器级别登录名的名称)Adding a database user with a different name than its server-level login

以下示例将 SQL ServerSQL Server 登录名 BjornR 添加到具有用户名 Bjorn 的当前数据库,并将数据库用户 Bjorn 添加到 Production 数据库角色。The following example adds SQL ServerSQL Server login BjornR to the current database that has a user name of Bjorn, and adds database user Bjorn to the Production database role.

EXEC sp_adduser 'BjornR', 'Bjorn', 'Production';  

请参阅See Also

安全存储过程 (Transact-SQL) Security Stored Procedures (Transact-SQL)
sys.server_principals (Transact-SQL) sys.server_principals (Transact-SQL)
sp_addrole (Transact-SQL) sp_addrole (Transact-SQL)
CREATE USER (Transact-SQL) CREATE USER (Transact-SQL)
sp_dropuser (Transact-SQL) sp_dropuser (Transact-SQL)
sp_grantdbaccess (Transact-SQL) sp_grantdbaccess (Transact-SQL)
sp_grantlogin (Transact-SQL) sp_grantlogin (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)