sp_helplogins (Transact-SQL)
Applies to: SQL Server
Provides information about logins and the users associated with them in each database.
Transact-SQL syntax conventions
Syntax
sp_helplogins [ [ @LoginNamePattern = ] N'LoginNamePattern' ]
[ ; ]
Arguments
[ @LoginNamePattern = ] N'LoginNamePattern'
@LoginNamePattern is sysname, with a default of NULL
.
A login name. @LoginNamePattern is sysname, with a default of NULL
. @LoginNamePattern must exist if specified. If @LoginNamePattern isn't specified, information about all logins is returned.
Return code values
0
(success) or 1
(failure).
Result set
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 doesn't have an associated user name. |
ARemote |
char(7) | Yes = LoginName has an associated remote login.No = LoginName doesn't 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) | Member of = 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
can't 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
Here is the result set.
LoginName SID DefDBName DefLangName AUser ARemote
--------- -------------------------- --------- ----------- ----- -------
John 0x23B348613497D11190C100C master us_english yes no
LoginName DBName UserName UserOrAlias
--------- ------ -------- -----------
John pubs John User
Related content
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho