sp_helplogins

Transact-SQL Reference

Transact-SQL Reference

sp_helplogins

Provides information about logins and the associated users in each database.

Syntax

sp_helplogins [ [ @LoginNamePattern = ] 'login' ]

Arguments

[@LoginNamePattern =] 'login'

Is a login name. login is sysname, with a default of NULL. login must exist if specified. If login is not specified, information about all logins is returned.

Return Code Values

0 (success) or 1 (failure)

Result Sets

The first report contains information about each login specified.

Column name Data type Description
LoginName sysname Login name.
SID varbinary(85) Login security identifier.
DefDBName sysname Default database that LoginName uses when connecting to Microsoft® SQL Server™.
DefLangName sysname Default language used by LoginName.
Auser char(5) Yes = LoginName has an associated user name in a database.
No = LoginName does not have an associated user name.
ARemote char(7) Yes = LoginName has an associated remote login.
No = LoginName does not have an associated login.

The second report contains information about users and aliases associated with each login.

Column name Data type Description
LoginName sysname Login name.
DBName sysname Default database that LoginName uses when connecting to SQL Server.
UserName sysname User account that LoginName is mapped to in DBName, and the roles that LoginName is a member of in DBName.
UserOrAlias char(8) MemberOf = UserName is a role.
User = UserName is a user account.

Remarks

Before removing logins, use sp_helplogins to determine the user accounts the login maps to.

Permissions

Only members of the sysadmin and securityadmin fixed server roles can execute sp_helplogins.

sp_helplogins must check all databases on the server to determine the user accounts in those databases associated with the logins. Therefore, for each database on the server, one of these must apply:

  • The user executing sp_helplogins must have permissions to access the database.

  • The guest user account must exist in the database.

If a database cannot be accessed, sp_helplogins displays error message 15622 and as much information as it can for logins associated with user accounts in those databases.

Examples

This 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

sp_helpuser

System Stored Procedures