Using syslogins to track if an account is locked

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

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

At login time, if the “enable last login updates” password policy option is set to “1,” the lastlogindate column is set to the current datetime and the previous value of the column is stored in the PSS of the login session.The update to syslogins and the PSS can occur at each login to Adaptive Server.The default value for this option in a new master database or an upgraded database is “1.” The administrator can choose to disable this option by executing the procedure:

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

@@lastlogindate is available to each user login session and is specific to each 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 used previously or “enable last login updates” is 0, then the value of @@lastlogindate is NULL.

Updates to column lastlogindate in syslogins are not logged in the transaction log, because adding a log record for every login would cause master database to fill up quickly.

An administrator with sso_role can lock login accounts that have been inactive for a given number of days using the following sp_locklogin command:

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 value of number of inactive days can be in the range 1 to 32767 (days).

When a login is locked using such a command, the lockreason column is updated with the reason. 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.