Chapter 1: System Procedures

sp_displaylogin

Description

Displays information about a login account. Also displays information about the hierarchy tree above or below the login account when you so specify.

Syntax

sp_displaylogin [loginame [, expand_up | expand_down]]

Parameters

loginame: is the user login account about which you want information if it is other than your own. You must be a System Security Officer or System Administrator to get information about someone else’s login account.

expand_up: specifies that Adaptive Server display all roles in the role hierarchy that contain the loginame role.

expand_down: specifies that Adaptive Server display all roles in the role hierarchy that are contained by the loginame role.

Examples

Example 1

Displays information about your server login account:

1> sp_displaylogin 'sa'
2> go

Suid: 1
Loginame: sa
Fullname: 
Default Database: master
Default Language: 
Auto Login Script: 
Configured Authorization:
        sa_role (default ON)
        sso_role (default ON)
        oper_role (default ON)
        sybase_ts_role (default ON)
Locked: NO
Date of Last Password Change: Jul 26 2005 10:42AM 
Password expiration interval: 0 
Password expired: NO 
Minimum password length: 6 
Maximum failed logins: 0 
Current failed login attempts: 
Authenticate with: NONE
(return status = 0)

Example 2

Displays information about the login account “susanne”. The information displayed varies, depending on the role of the user executing sp_displaylogin:

sp_displaylogin susanne

Suid: 12
Loginame: susanne
Fullname: 
Default Database: pubs2
Default Language: 
Auto Login Script:
Configured Authorization:
    supervisor (default OFF)
Locked: NO
Date of Last Password Change: May 12 1997 11:09AM 
Authenticate with: NONE

Example 3

Displays information about all roles containing the role of the login account “pillai”. The information displayed varies, depending on the role of the user executing sp_displaylogin:

sp_displaylogin pillai, expand_up

Example 4

Displays the login security-related parameters configured for a login, as well as a specified authentication mechanism:

sp_displaylogin joe

Suid: 294
Loginame: joe
Fullname: Joseph Resu
Default Database: master
Default Language: 
Auto Login Script:
Configured Authorization: 
    intern_role (default OFF)
Locked: NO
Date of Last Password Change: Nov 24 1998 3:46PM
Password expiration interval : 5
Password expired : NO
Minimum password length:4
Maximum failed logins : 10
Current failed logins : 3
Authenticate with: ASE

Usage

sp_displaylogin displays configured roles, so even if you have made a role inactive with the set command, it is displayed.
If there are any login triggers associated with the login in question, they are listed after the Auto Login Script line. For more information, see “Row-level access control” in Chapter 11, “Managing User Permissions,” of the System Administration Guide.
When you use sp_displaylogin to get information about your own account, you do not need to use the loginame parameter. sp_displaylogin displays your server user ID, login name, full name, any roles that have been granted to you, date of last password change, default database, default language, and whether your account is locked.
If you are a System Security Officer or System Administrator, you can use the loginame parameter to access information about any account.

Permissions

Only a System Administrator or a System Security Officer can execute sp_displaylogin with the loginame and expand parameters to get information about other users’ login accounts. Any user can execute sp_displaylogin to get information about his or her own login account.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect