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

提供有关每个数据库中的登录名以及与其相关的用户的信息。Provides information about logins and the users associated with them in each database.

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

语法Syntax

  
sp_helplogins [ [ @LoginNamePattern = ] 'login' ]  

参数Arguments

[ @LoginNamePattern = ] 'login'是登录名。[ @LoginNamePattern = ] 'login' Is a login name. login的值为sysname,默认值为 NULL。login is sysname, with a default of NULL. 如果指定,则必须存在登录名login must exist if specified. 如果未指定login ,则返回有关所有登录名的信息。If login is not specified, information about all logins is returned.

返回代码值Return Code Values

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

结果集Result Sets

第一个报告包含有关指定的每个登录的信息,如下表所示。The first report contains information about each login specified, as shown in the following table.

列名称Column name 数据类型Data type 说明Description
LoginNameLoginName sysnamesysname 登录名。Login name.
SIDSID varbinary (85)varbinary(85) 登录安全标识符 (SID)。Login security identifier (SID).
DefDBNameDefDBName sysnamesysname LoginName在连接到实例时使用的默认数据库SQL ServerSQL ServerDefault database that LoginName uses when connecting to an instance of SQL ServerSQL Server.
DefLangNameDefLangName sysnamesysname LoginName使用的默认语言。Default language used by LoginName.
AuserAuser char (5)char(5) Yes = LoginName在数据库中具有关联的用户名。Yes = LoginName has an associated user name in a database.

No = LoginName没有关联的用户名。No = LoginName does not have an associated user name.
ARemoteARemote char (7)char(7) Yes = LoginName具有关联的远程登录名。Yes = LoginName has an associated remote login.

No = LoginName没有关联的登录名。No = LoginName does not have an associated login.

第二个报告包含有关映射到每个登录的用户的信息以及登录的角色成员身份,如下表所示。The second report contains information about users mapped to each login, and the role memberships of the login as shown in the following table.

列名称Column name 数据类型Data type 说明Description
LoginNameLoginName sysnamesysname 登录名。Login name.
DBNameDBName sysnamesysname LoginName在连接到实例时使用的默认数据库SQL ServerSQL ServerDefault database that LoginName uses when connecting to an instance of SQL ServerSQL Server.
用户名UserName sysnamesysname LoginNamedbname中映射到的用户帐户,以及LoginNamedbname中的成员的角色。User account that LoginName is mapped to in DBName, and the roles that LoginName is a member of in DBName.
UserOrAliasUserOrAlias char (8)char(8) MemberOf =用户名是一个角色。MemberOf = UserName is a role.

User = UserName是用户帐户。User = UserName is a user account.

备注Remarks

删除登录名之前,请使用sp_helplogins标识映射到该登录名的用户帐户。Before removing a login, use sp_helplogins to identify user accounts that are mapped to the login.

权限Permissions

要求具有securityadmin固定服务器角色的成员身份。Requires membership in the securityadmin fixed server role.

若要确定映射到给定登录名的所有用户帐户, sp_helplogins必须检查服务器中的所有数据库。To identify all user accounts mapped to a given login, sp_helplogins must check all databases within the server. 因此,对于服务器中的每个数据库,至少应满足下列条件之一:Therefore, for each database on the server, at least one of the following conditions must be true:

  • 正在执行sp_helplogins的用户有权访问数据库。The user that is executing sp_helplogins has permission to access the database.

  • 在数据库中启用了guest用户帐户。The guest user account is enabled in the database.

如果sp_helplogins无法访问数据库, sp_helplogins将返回尽可能多的信息,并显示错误消息15622。If sp_helplogins cannot access a database, sp_helplogins will return as much information as it can and display error message 15622.

示例Examples

以下示例报告有关登录 John 的信息。The following example reports information about the login John.

EXEC sp_helplogins 'John';  
GO  
  
LoginName SID                        DefDBName DefLangName AUser ARemote   
--------- -------------------------- --------- ----------- ----- -------   
John      0x23B348613497D11190C100C  master    us_english  yes   no  
  
(1 row(s) affected)  
  
LoginName   DBName   UserName   UserOrAlias   
---------   ------   --------   -----------   
John        pubs     John       User          
  
(1 row(s) affected)  

另请参阅See Also

安全存储过程 (Transact-sql) Security Stored Procedures (Transact-SQL)
sp_helpdb (Transact-sql) sp_helpdb (Transact-SQL)
sp_helpuser (Transact-sql) sp_helpuser (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)