sp_displaylogin

Description

Displays information about a login account. By using a wildcard expression (%) and a user ID, you can also obtain information about matching logins. Also displays information about the hierarchy tree above or below the login account when you so specify, and displays the encryption versions used for a login.

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: mylogin
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: Aug 10 2006 11:17AM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: NONE
Login password encryption: SYB-PROP, SHA-256
Last login date : Aug 17 2006 5:55PM
(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. There is not password expiration set for user “susanne”, so the password does not expire.

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: July 26 2007 10:42AM 
Authenticate with: NONE

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, 2007 at 3:46PM, and expires five days later, on December 5, 2007 at 3:46PM.

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 2007 3:46PM
Password expiration interval : 5
Password expired : NO
Minimum password length:4
Maximum failed logins : 10
Current failed logins : 3
Authenticate with: ASE
Login password encryption: SHA-256

Example 4

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

sp_displaylogin '1'
-------------
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: Dec 18 2006
Password expiration interval: 0

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  Default Database  Default Language  Auto Login Script 
Locked  Date of Last Password Change  Password expiration interval  Password 
expired  Minimum password length  Maximum failed logins  Current failed login 
attempts  Authenticate with 
Configured Authorization---- --------- --------- --------------- ---------------- ----------------- -------- --------------------------- --------------------------------------- --------------------------- ---------------------------------- -------------------------------- ---------------------------------------- ---------------------------- --------------------------------------------------------------------------------------------

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

Example 6

In this example, both the old Sybase proprietary encryption algorithm is used as well as the algorithm SHA-256 :

1> sp_displaylogin 'mylogin'
2> go

Suid: 121
Loginame: mylogin
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: Aug 10 2006 11:17AM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: NONE
Login password encryption: SYB-PROP, SHA-256
Last login date : Aug 17 2006 5:55PM
(return status = 0)

When encryption is used, 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 2006 5:55PM
(return status = 0)

When a login has not occurred after upgrade, 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: 
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: YES
        Date when locked: Aug 18 2006 9:15AM
        Reason: Account locked by Adaptive Server due to failed login attempts reaching max failed logins.
        Locking suid: 121
Date of Last Password Change: Aug 10 2006 11:17AM 
Password expiration interval: 0 
Password expired: NO 
Minimum password length: 6 
Maximum failed logins: 3 
Current failed login attempts: 3
Authenticate with: ANY 
Login password encryption: SYB-PROP, SHA-256
Last login date : Aug 17 2006 5:55PM 
(return status = 0)

Example 7

Displays the encryption versions used for a login; the last line of this output includes information about the login encryption Adaptive Server uses:

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: Mar  8 2007  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

If Adaptive Server uses encryption algorithms from earlier and 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

When a login has been locked, sp_displaylogin shows the date, reason, and login that locked the account.

Example 8

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.
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 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