sp_displaylogin

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

Display Information About Server Login Account – The password expiration is set to 0, indicating the password never expires:

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

Display Information About 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
```

Display Login Security-Related Parameters Configured for a Login – 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

Display Information About 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

Use Wildcard to Indicate Any Server Login Account – 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

Display Encrypted and Stored On-disk Login Password – 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 SAP ASE 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 SAP ASE 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)

Display Encryption Versions Used for a Login – Displays the encryption versions used for a login; this output includes information about the on-disk login password encryption the SAP ASE 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 the SAP ASE server uses encryption algorithms from SAP ASE 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

Display Login and Password Policy Options of 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

Display Login Account for User with Suid 56 –
```
sp_displaylogin '56'
```
Display Login Account Information for All Users With Logins Begin With “st” –
```
sp_displaylogin 'st%'
```

Usage

There are additional considerations when using sp_displaylogin:

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

The permission checks for sp_displaylogin differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage any login` privilege or `manage sever` privilege. Any user can execute sp_displaylogin to display information about their own login account.
Disabled	With granular permissions disabled, you must be a user with sa_role or sso_role. Any user can execute sp_displaylogin to display information about their own login account.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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