LOGINPROPERTY

Returns information about a SQL login: its failed sign-in attempts, its last sign-in and its default database and language.

Syntax

LOGINPROPERTY ( 'login_name' , 'property_name' )

Arguments

‘login_name’

Is the name of the Querona login to ask about. login_name is sysname and is matched ignoring case. Only a standard SQL login has these properties: a Windows login, a Microsoft Entra ID login and a service account answer NULL, as a Windows login does on SQL Server.

‘property_name’

Is the name of the property to return. property_name is nvarchar(128) and is matched ignoring case. It can be one of the following values.

Property	Base type	What Querona answers	Difference from SQL Server
BadPasswordCount	int	How many attempts for the login were refused since its last successful sign-in, from logon tracking; 0 when nothing has been recorded, NULL while tracking is off.	Querona counts every refused attempt that named the login - a wrong password, a disabled account, an attempt refused during a lockout or from a network the login is not trusted from - and the count is merged across the nodes of a cluster. SQL Server counts wrong passwords only, only for a login with CHECK_POLICY = ON, and on Linux the count stays at 1 however many follow. Read only by the login itself or by a caller holding ALTER ANY LOGIN or VIEW ANY DEFINITION, where SQL Server answers it about sa to every login (see Remarks).
BadPasswordTime	datetime	When an attempt for the login was last refused, in server time; the sentinel 1900-01-01 when none ever was, NULL while tracking is off.	The last refusal of any of the kinds counted above, not only a wrong password; the sentinel is the plain date. Read under the same narrower rule as BadPasswordCount (see Remarks).
DefaultDatabase	nvarchar	master, the default database of every login.	None.
DefaultLanguage	nvarchar	us_english, the default language of every login.	None.
IsLocked, LockoutTime	int, datetime	NULL: Querona has no lock on a login. The failed sign-in lockout rate-limits a login and origin pair on one node and is not a lock on the login.	SQL Server reports 0 or 1 and the moment the login was locked out.
IsExpired, IsMustChange, DaysUntilExpiration, PasswordLastSetTime	int, int, int, datetime	NULL: Querona does not expire passwords.	SQL Server reports the expiry and must-change state of a login with CHECK_EXPIRATION = ON.
HistoryLength	int	NULL: Querona keeps no password history.	SQL Server reports the policy’s history length for a login with CHECK_POLICY = ON.
PasswordHash	varbinary	NULL, whoever asks: Querona never discloses a credential.	SQL Server returns the hash to a caller with CONTROL SERVER.
PasswordHashAlgorithm	int	NULL: Querona stores passwords in its own format, which has no SQL Server algorithm code.	SQL Server reports the version of its hash.

Querona adds three property names for what SQL Server has no property for. On SQL Server they are unknown names and answer NULL, so a query that uses them runs on both.

Property	Base type	What Querona answers
LastSuccessfulLogonTime	datetime	When the login last signed in successfully, in server time; the sentinel 1900-01-01 when it never has since tracking was installed, NULL while tracking is off.
LastLogonClientInterfaceName	nvarchar	The protocol the last successful sign-in came in over: TDS for a SQL client, HTTP for the web interface and the REST API; NULL when the login has not signed in or tracking is off.
LastLogonProgramName	nvarchar	The program name the client of the last successful sign-in declared; NULL when the login has not signed in, the client declared none, or tracking is off.

Any other property name returns NULL.

Return types

sql_variant

The base type of the value is the one listed for the property. NULL is returned, never an error, when either argument is NULL, the property is unknown, the login does not exist, the login is not a standard SQL login, or the caller may not see the login.

Remarks

Which logins a caller may see is the rule SQL Server applies to sys.sql_logins: a login always sees itself and the login named admin (as every login sees sa on SQL Server), and a caller holding ALTER ANY LOGIN or VIEW ANY DEFINITION sees every login. Every other login answers NULL. VIEW SERVER STATE does not widen this, although it does show every session’s previous sign-in in sys.dm_exec_sessions. The rule goes by the name: an installation that renamed its administrator at setup has no login that everyone sees, and a login created later under the name admin is visible to everyone. sys.server_principals is not narrowed and still lists every login.

Five properties are read under a narrower rule: BadPasswordCount, BadPasswordTime and the three Querona properties of the last sign-in. Another login’s, the admin login’s included, are answered only to a caller holding ALTER ANY LOGIN or VIEW ANY DEFINITION; a login always reads its own. They are judged together because they describe one thing - when a login signs in and how often an attempt for it is refused - and because the refusal count returns to zero at a successful sign-in, so a caller that may read the count may also tell when the sign-in happened. SQL Server answers BadPasswordCount and BadPasswordTime about its sa to every login; Querona does not, because its counter measures more: it accumulates, it counts every refused attempt that named the login rather than wrong passwords only, and it is merged across a cluster.

A datetime property is reported in server time. One whose event never happened returns 1900-01-01 00:00:00, wherever the server runs. SQL Server has been observed to report that sentinel shifted by the offset of its host’s time zone (02:00:00 on a UTC+2 host), so test for the date rather than for NULL or a fixed hour. Inside the sql_variant the value travels as datetime2, where SQL Server’s base type is datetime; it reads as a date and time on every client, and SQL_VARIANT_PROPERTY(..., 'BaseType') is not answered by Querona.

The logon-sourced properties come from logon tracking. They answer NULL while tracking is off, and the “nothing recorded” values - 0, the sentinel, NULL - for a login nothing has been recorded for.

Examples

The calling login’s own failed attempts since it last signed in, and when it last did:

SELECT LOGINPROPERTY(SUSER_SNAME(), 'BadPasswordCount') AS bad_password_count,
       LOGINPROPERTY(SUSER_SNAME(), 'BadPasswordTime') AS bad_password_time,
       LOGINPROPERTY(SUSER_SNAME(), 'LastSuccessfulLogonTime') AS last_successful_logon_time;

Every SQL login with its last sign-in and the client it came from, for a caller holding ALTER ANY LOGIN or VIEW ANY DEFINITION (any other caller gets its own row answered and NULL for the admin row); a login that has never signed in shows the sentinel date:

SELECT name,
       CAST(LOGINPROPERTY(name, 'LastSuccessfulLogonTime') AS datetime) AS last_successful_logon_time,
       CAST(LOGINPROPERTY(name, 'LastLogonClientInterfaceName') AS nvarchar(128)) AS client_interface,
       CAST(LOGINPROPERTY(name, 'LastLogonProgramName') AS nvarchar(128)) AS program_name
FROM sys.sql_logins
ORDER BY name;

See Also

• Functions

• Logon tracking settings

• Users and roles

• Failed sign-in lockout