| title | sp_helplogins (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | f9ad3767-5b9f-420d-8922-b637811404f7 | ||
| author | stevestein | ||
| ms.author | sstein |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Provides information about logins and the users associated with them in each database.
Transact-SQL Syntax Conventions
sp_helplogins [ [ @LoginNamePattern = ] 'login' ]
[ @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.
0 (success) or 1 (failure)
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 [!INCLUDEssNoVersion]. |
| 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 mapped to each login, and the role memberships of the 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 [!INCLUDEssNoVersion]. |
| 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. |
Before removing a login, use sp_helplogins to identify user accounts that are mapped to the login.
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.
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)
Security Stored Procedures (Transact-SQL)
sp_helpdb (Transact-SQL)
sp_helpuser (Transact-SQL)
System Stored Procedures (Transact-SQL)