Export (0) Print
Expand All
Expand Minimize
3 out of 4 rated this helpful - Rate this topic

sp_helplogins (Transact-SQL)

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

Topic link icon 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 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.

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)
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.