sp_addlogin (Transact-SQL)sp_addlogin (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

创建新的 SQL ServerSQL Server 登录名,该登录名允许用户使用 SQL ServerSQL Server 身份验证连接到 SQL ServerSQL Server 实例。Creates a new SQL ServerSQL Server login that allows a user to connect to an instance of SQL ServerSQL Server by using SQL ServerSQL Server authentication.

重要

此功能处于维护模式并且可能会在 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 LOGIN相反。Use CREATE LOGIN instead.

重要

请尽可能使用 Windows 身份验证。When possible, use Windows authentication.

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

语法Syntax

  
sp_addlogin [ @loginame = ] 'login'   
    [ , [ @passwd = ] 'password' ]   
    [ , [ @defdb = ] 'database' ]   
    [ , [ @deflanguage = ] 'language' ]   
    [ , [ @sid = ] sid ]   
    [ , [ @encryptopt = ] 'encryption_option' ]   
[;]  

参数Arguments

[ @loginame=] '登录名[ @loginame= ] 'login'
登录的名称。Is the name of the login. 登录名sysname ,无默认值。login is sysname, with no default.

[ @passwd= ] 'password'[ @passwd= ] 'password'
登录的密码。Is the login password. 密码sysname,默认值为 NULL。password is sysname, with a default of NULL.

重要

不要使用空密码。Do not use a blank password. 请使用强密码。Use a strong password.

[ @defdb=] '数据库[ @defdb= ] 'database'
登录的默认数据库(在登录后登录首先连接到该数据库)。Is the default database of the login (the database to which the login is first connected after logging in). 数据库sysname,默认值为database is sysname, with a default of master.

[ @deflanguage=] '语言[ @deflanguage= ] 'language'
登录的默认语言。Is the default language of the login. 语言sysname,默认值为 NULL。language is sysname, with a default of NULL. 如果语言未指定,默认语言的新登录名设置为服务器的当前默认语言。If language is not specified, the default language of the new login is set to the current default language of the server.

[ @sid=] 'sid[ @sid= ] 'sid'
安全标识号 (SID)。Is the security identification number (SID). sidvarbinary(16) ,默认值为 NULL。sid is varbinary(16), with a default of NULL. 如果sid为 NULL,则系统将生成新的登录名的 SID。If sid is NULL, the system generates a SID for the new login. 尽管使用varbinary数据类型,NULL 以外的值必须是 16 个字节的长度,并且不能已存在。Despite the use of a varbinary data type, values other than NULL must be exactly 16 bytes in length, and must not already exist. 指定sid非常有用,例如,当您要编写脚本或移动SQL ServerSQL Server从一台服务器的登录名到另一个并且您想要在不同服务器上使用相同的 SID 的登录名。Specifying sid is useful, for example, when you are scripting or moving SQL ServerSQL Server logins from one server to another and you want the logins to have the same SID on different servers.

[ @encryptopt=] 'encryption_option[ @encryptopt= ] 'encryption_option'
指定是以明文形式,还是以明文密码的哈希运算结果来传递密码。Specifies whether the password is passed in as clear text or as the hash of the clear text password. 注意,不进行加密。Note that no encryption takes place. 在本讨论中使用“加密”一词是为了向后兼容。The word "encrypt" is used in this discussion for the sake of backward compatibility. 如果传入明文密码,将对它进行哈希运算。If a clear text password is passed in, it is hashed. 哈希值将存储起来。The hash is stored. encryption_optionvarchar (20) ,可以是下列值之一。encryption_option is varchar(20), and can be one of the following values.

Value 描述Description
NULLNULL 以明文形式传递密码。The password is passed in clear. 这是默认设置。This is the default.
skip_encryptionskip_encryption 密码已经过哈希运算。The password is already hashed. 数据库引擎Database Engine应存储值,且不对其重新进行哈希运算。The 数据库引擎Database Engine should store the value without re-hashing it.
skip_encryption_oldskip_encryption_old 所提供的密码由 SQL ServerSQL Server 的早期版本进行哈希运算。The supplied password was hashed by an earlier version of SQL ServerSQL Server. 数据库引擎Database Engine应存储值,且不对其重新进行哈希运算。The 数据库引擎Database Engine should store the value without re-hashing it. 提供该选项只是为了升级。This option is provided for upgrade purposes only.

返回代码值Return Code Values

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

备注Remarks

SQL ServerSQL Server 登录名可以包含 1 到 128 个字符,其中包括字母、符号和数字。logins can contain from 1 to 128 characters, including letters, symbols, and numbers. 登录名不能包含反斜杠 (\); 可以是保留的登录名,例如 sa 或 public,或已经存在; 或者为 NULL 或空字符串 ('')。Logins cannot contain a backslash (\); be a reserved login name, for example sa or public, or already exist; or be NULL or an empty string ('').

如果提供默认数据库的名称,则不用执行 USE 语句就可以连接到指定的数据库。If the name of a default database is supplied, you can connect to the specified database without executing the USE statement. 但是,不能使用的默认数据库,直到你被授予访问权限的数据库由数据库所有者 (通过使用sp_addusersp_addrolemember) 或sp_addrole.However, you cannot use the default database until you are given access to that database by the database owner (by using sp_adduser or sp_addrolemember) or sp_addrole.

SID 号是一个 GUID,用于唯一地标识服务器中的登录名。The SID number is a GUID that will uniquely identify the login in the server.

更改服务器的默认语言将不会更改现有登录的默认语言。Changing the default language of the server does not change the default language of existing logins. 若要更改服务器的默认语言,请使用sp_configureTo change the default language of the server, use sp_configure.

使用skip_encryption来取消密码哈希非常有用如果时该登录名添加到已对密码SQL ServerSQL ServerUsing skip_encryption to suppress password hashing is useful if the password is already hashed when the login is added to SQL ServerSQL Server. 如果密码已哈希处理的早期版本的SQL ServerSQL Server,使用skip_encryption_oldIf the password was hashed by an earlier version of SQL ServerSQL Server, use skip_encryption_old.

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

下表显示了数个与 sp_addlogin 一起使用的存储过程。The following table shows several stored procedures that are used with sp_addlogin.

存储过程Stored procedure 描述Description
sp_grantloginsp_grantlogin 添加 Windows 用户或组。Adds a Windows user or group.
sp_passwordsp_password 更改用户密码。Changes the password of a user.
sp_defaultdbsp_defaultdb 更改用户的默认数据库。Changes the default database of a user.
sp_defaultlanguagesp_defaultlanguage 更改用户的默认语言。Changes the default language of a user.

权限Permissions

需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

示例Examples

A.A. 创建 SQL Server 登录Creating a SQL Server login

以下示例为用户 Victoria 创建 SQL ServerSQL Server 登录,密码为 B1r12-36,并且不指定默认数据库。The following example creates a SQL ServerSQL Server login for the user Victoria, with a password of B1r12-36, without specifying a default database.

EXEC sp_addlogin 'Victoria', 'B1r12-36';  
GO  

B.B. 创建具有默认数据库的 SQL Server 登录Creating a SQL Server login that has a default database

以下示例为用户 Albert 创建 SQL ServerSQL Server 登录,密码为 B5432-3M6,默认数据库为 corporateThe following example creates a SQL ServerSQL Server login for the user Albert, with a password of B5432-3M6 and a default database of corporate.

EXEC sp_addlogin 'Albert', 'B5432-3M6', 'corporate';  
GO  

C.C. 创建具有不同默认语言的 SQL Server 登录Creating a SQL Server login that has a different default language

以下示例为用户 TzTodorov 创建 SQL ServerSQL Server 登录,密码为 709hLKH7chjfwv,默认数据库为 AdventureWorks2012,默认语言为 BulgarianThe following example creates a SQL ServerSQL Server login for the user TzTodorov, with a password of 709hLKH7chjfwv, a default database of AdventureWorks2012, and a default language of Bulgarian.

EXEC sp_addlogin 'TzTodorov', '709hLKH7chjfwv', 'AdventureWorks2012', N'български'  

D.D. 创建具有特定 SID 的 SQL Server 登录Creating a SQL Server login that has a specific SID

以下示例为用户 Michael 创建 SQL ServerSQL Server 登录名,密码为 B548bmM%f6,默认数据库为 AdventureWorks2012,默认语言为 us_english,SID 为 0x0123456789ABCDEF0123456789ABCDEFThe following example creates a SQL ServerSQL Server login for the user Michael, with a password of B548bmM%f6, a default database of AdventureWorks2012, a default language of us_english, and a SID of 0x0123456789ABCDEF0123456789ABCDEF.

EXEC sp_addlogin 'Michael', 'B548bmM%f6', 'AdventureWorks2012', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF  

请参阅See Also

CREATE LOGIN (Transact-SQL) CREATE LOGIN (Transact-SQL)
sp_droplogin (Transact-SQL) sp_droplogin (Transact-SQL)
sp_helpuser (TRANSACT-SQL) sp_helpuser (Transact-SQL)
sp_revokelogin (Transact-SQL) sp_revokelogin (Transact-SQL)
xp_logininfo (Transact-SQL)xp_logininfo (Transact-SQL)