sp_displaylogin

Description

Displays information about a login account. By using a wildcard expression (%), you can also obtain information about matching logins. Also displays the encryption versions of the login password stored on disk.

Syntax

sp_displaylogin ['user_id' | '[loginame | wildcard]'

Parameters

user_id: is the server user ID.

loginame: is the user login account about which you want information. You must be a system security officer or system administrator to get information about someone else’s login account.

wildcard: is the wildcard expression you use to obtain information about login accounts.

Examples

Example 1

Displays information about your server login account. The password expiration is set to “0”, indicating the password will never expire.

1> sp_displaylogin 'sa'
2> go

Suid: 121
Loginame: sa
Fullname:
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: Aug 10 2010 11:17AM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Login password encryption: SYB-PROP, SHA-256
Last login date : Aug 17 2010 5:55PM
Login Profile :emp_lp

Example 2

Displays information about the login account “susanne”. The information displayed varies, depending on the role of the user executing sp_displaylogin. There is not password expiration set for user “susanne”, so the password does not expire.

sp_displaylogin susanne

Suid: 12
Loginame: susanne
Fullname: 
Configured Authorization:
    supervisor (default OFF)
Locked: NO
Date of Last Password Change: July 26 2010 10:42AM 
Login Profile :emp_lp

Example 3

Displays the login security-related parameters configured for a login, as well as a specified authentication mechanism. The password expires on November 29, 2010 at 3:46PM, and expires five days later, on December 5, 2010 at 3:46PM.

sp_displaylogin joe

Suid: 294
Loginame: joe
Fullname: Joseph Resu
Configured Authorization: 
    intern_role (default OFF)
Locked: NO
Date of Last Password Change: Nov 24 2010 3:46PM
Password expiration interval : 5
Password expired : NO
Minimum password length:4
Maximum failed logins : 10
Current failed logins : 3
Login password encryption: SHA-256
Login Profile :emp_lp

Example 4

Displays information about a login account with Server User ID 1.

sp_displaylogin '1'
-------------
Suid: 1
Loginame: sa
Fullname:
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: Dec 18 2010
Password expiration interval: 0
Login Profile :emp_lp

Example 5

You can use a wildcard to indicate any server login account, as opposed to your own server login account.

sp_displaylogin '%'
--------------------

Suid  Loginname  Fullname  Locked  Date of Last Password Change 
Password expiration interval  Password expired  Minimum password length  Maximum failed logins  Current failed login attempts  Authenticate with  Login Profile  Configured Authorization---- --------- --------- --------------- ---------------- ----------------- -------- --------------------------- --------------------------------------- --------------------------- ---------------------------------- -------------------------------- ---------------------------------------- ---------------------------- --------------------------------------------------------------------------------------------

2 probe NULL sybsystemdb NULL NULL NO Jan  8 2010 7:13AM 1 NO 6 0 0 NONE
NULL
1 sa NULL master NULL NULL NO Jan  8 2010 6:46AM 1 NO 6 0 0 NONE

Example 6

The on-disk login password is encrypted and stored, using both the old Sybase proprietary encryption algorithm and the SHA-256 algorithm:

1> sp_displaylogin 'mylogin'
2> go

Suid: 121
Loginame: mylogin
Fullname:
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: Aug 10 2006 11:17AM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Login password encryption: SYB-PROP, SHA-256
Last login date : Aug 17 2010 5:55PM 
Login Profile :emp_lp

(return status = 0)

When the login password is stored on disk using the SHA-256 algorithm only, the output of sp_displaylogin has the line “Login password encryption: SHA-256”:

1> sp_displaylogin 'mylogin'
2> go

Suid: 121
Loginame: mylogin
 ...
Authenticate with: NONE 
Login password encryption: SHA-256
Last login date : Aug 17 2010 5:55PM 
Login Profile :emp_lp

(return status = 0)

When a login has not occurred after upgrade from Adaptive Server versions earlier than 15.0.2, the previous style of encryption is still in place, and the output of sp_displaylogin has the line “Login password encryption: SYB-PROP”:

1> sp_displaylogin 'mylogin'
2> go

Suid: 121
Loginame: mylogin
 ...
Authenticate with: NONE 
Login password encryption: SYB-PROP 
Last login date : Aug 17 2006 5:55PM 
(return status = 0)

When a login has been locked, sp_displaylogin shows the date, reason, and login that locked the account. The lastlogindate value is also displayed:

1> sp_displaylogin 'mylogin'
2> go

Suid: 121
Loginame: mylogin
Fullname: 
Configured Authorization:
        sa_role (default ON)
        sso_role (default ON)
        oper_role (default ON)
        sybase_ts_role (default ON)
Locked: YES
        Date when locked: Aug 18 2010 9:15AM
        Reason: Account locked by Adaptive Server due to failed login attempts reaching max failed logins.
        Locking suid: mylogin
Date of Last Password Change: Aug 10 2010 11:17AM 
Password expiration interval: 0 
Password expired: NO 
Minimum password length: 6 
Maximum failed logins: 3 
Current failed login attempts: 3
Login password encryption: SYB-PROP, SHA-256
Last login date : Aug 17 2010 5:55PM
Login Profile :emp_lp 
(return status = 0)

Example 7

Displays the encryption versions used for a login; this output includes information about the on-disk login password encryption Adaptive Server uses:

sp_displaylogin sa
go

Suid: 1
Loginame: sa
Fullname:
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: Mar  8 2010 3:04PM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Login Password Encryption: SHA-256
Login Profile :emp_lp

If Adaptive Server uses encryption algorithms from Adaptive Server versions earlier than 15.0.2 or the current release during a downgrade period, sp_displaylogin displays the earlier Sybase proprietary encryption algorithm and the new algorithm, SHA-256:

Login password encryption: SYB-PROP, SHA-256

Example 8

Displays the login and password policy options of the current login account :

sp_displaylogin 
go

Suid: 5
Loginame: tammi
Fullname:
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: Mar  8 2010 3:04PM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
Login Password Encryption: SHA-256

Exempt inactive lock: 0

Login Profile: emp_lp

Example 9

Displays the login account for the user with a suid of 56:

sp_displaylogin '56'

Displays the login account information for all users whose logins begin with “st”:

sp_displaylogin 'st%'

Usage

The sp_passwordpolicy security options are taken into consideration when displaying login information related to password expiration, maximum failed logins, and password length.

sp_displaylogin displays the encryption version(s) used for a login. For example, when both old and new encryption is used during the password downgrade period, the output of sp_displaylogin has the new line “Password encryption.”

sp_displaylogin displays configured roles, so even if you have made a role inactive with the set command, it is displayed.
Login triggers associated with the login in question are specified through a login profile. For more information, see “Managing login accounts and login profiles,” in 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, login profile, full name, any roles that have been granted to you, date of last password change, 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 or suid 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