Using syslogins to track if an account is locked

syslogins includes the lastlogindate, crdate, locksuid, lockreason, and lockdate columns to support the last login, and locking inactive accounts, letting an account owner or administrator know if an account is locked, when it was locked, who locked it, and the reason why it was locked.

At login creation, the crdate column is set to the current time.

If the enable last login updates password policy option is set to 1, the lastlogindate column is set to the datetime of the login, and the previous value of the column is stored in the process status structure of the login session. The update to syslogins and the process status structure can occur at each login to Adaptive Server. The default value for enable last login updates a new master database or an upgraded database is 1. To disable this option execute the procedure using administrator privileges:

sp_passwordpolicy 'set', 'enable last login updates', '0'

@@lastlogindate is specific to each user login session, and can be used by that session to determine the date and time of the previous login to the account. If the account has not been previously used or if enable last login updates is 0, the value of @@lastlogindate is NULL.

The transaction log does not log updates to syslogins..lastlogindate.

Administrators with sso_role can lock login accounts that are inactive for a given number of days, using:

sp_locklogin 'all', 'lock', [@except], 'number of inactive days'

This command has no effect if enable last login updates is set to 0 or the value of the lastlogindate column is NULL. The range of values for number of inactive days is 1 – 32767 (days).

The lockreason column specifies the reason a login was locked. The value of the lockdate column is set to the current datetime.

When an account is unlocked, columns lockreason, lockdate, and locksuid are reset to NULL.

The lockdate, locksuid, and lockreason columns are set internally by Adaptive Server. Table 3-3 provides lockreason values and descriptions, and the value of locksuid.

**Table 3-3: The reasons and values of locksuid**
Values for lockreason	Values for locksuid	Description of lockreason account
NULL	NULL	Account has not been locked.
0	suid of caller of sp_locklogin	Account locked by locksuid by manually executing sp_locklogin.
1	suid of caller of sp_locklogin	Account locked due to account inactivity, locksuid has manually executed sp_locklogin 'all', 'lock', 'ndays'.
2	suid of attempted login	Account locked by Adaptive Server due to failed login attempts reaching maximum failed logins.
3	suid of caller of sp_passwordpolicy set, "allow password downgrade", 0	Account locked by locksuid as the password downgrade period has ended, and login or role has not transitioned to SHA-256.
4	NULL	Account locked due to account inactivity.