sp_helplogins (Transact-SQL)
Provides information about logins and the users associated with them in each database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
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, as shown in the following table.
| Column name | Data type | Description |
|---|---|---|
LoginName |
sysname |
Login name. |
SID |
varbinary(85) |
Login security identifier (SID). |
DefDBName |
sysname |
Default database that LoginName uses when connecting to an instance of 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 associated with each login, as shown in the following table.
| Column name | Data type | Description |
|---|---|---|
LoginName |
sysname |
Login name. |
DBName |
sysname |
Default database that LoginName uses when connecting to an instance of 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 a login, use sp_helplogins to identify user accounts that are mapped to the login.
Permissions
Requires membership in the securityadmin fixed server role.
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:
- The user that is executing sp_helplogins has permission to access the database.
- The guest user account is enabled in the database.
If sp_helplogins cannot access a database, sp_helplogins will return as much information as it can and display error message 15622.
Examples
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
Reference
Security Stored Procedures (Transact-SQL)
sp_helpdb (Transact-SQL)
sp_helpuser (Transact-SQL)
System Stored Procedures (Transact-SQL)