sp_helplogins (Transact-SQL)
Provides information about logins and the users associated with them in each database.
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 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 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. |
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)
