SQL Server: Find users login time

Finding users who never logins is not trivial in SQL Server. But we can do it by creating a logon trigger.

To find users who never login in SQL Server we need to:

• Create a logon trigger (SQL code that is executed on login);
• Log accesses from this trigger.
• Periodically query the access log table.

The access table

To create the login access table:

USE logs
GO
CREATE TABLE login_access (
    username SYSNAME,
    last_login DATETIME2 DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (username, last_login)
)
GO

When adding a row we can specify username only. By default, last_login will contain the current UTC datetime.

The login trigger

To create the trigger:

CREATE TRIGGER trg_log_access
    ON ALL SERVER
    FOR LOGON
AS BEGIN
    INSERT INTO login_access (username) VALUES(SUSER_NAME());
END;

On logon, the trigger records the login. SUSER_NAME() returns the login identification name.

Informative queries

List users who never logged in since the trigger creation:

SELECT dp.name
    FROM sys.database_principals dp
    LEFT JOIN logs.login_access la
        ON dp.name = la.username
    WHERE la.username IS NULL
GO

List users who didn’t login in the last month:

SELECT dp.name
    FROM sys.database_principals dp
    LEFT JOIN logs.login_access la
        ON dp.name = la.username
        AND la.last_login >= DATEADD(month, -1, SYSUTCDATETIME())
    WHERE la.username IS NULL
    FETCHS FIRST 1 ROW ONLY
GO

DATEADD(month, -1, SYSUTCDATETIME()) returns the current UTC datetime minus one month, as DATETIME2.

Get a user last login time:

SELECT MAX(last_login)
    FROM logs.login_access
    WHERE username = '...'
GO