SQL Server Last Login Date for User

In this comprehensive tutorial, I’ll share my experience and walk you through different methods to retrieve the last login date for SQL Server users. Whether you’re managing SQL Server 2016, 2019, or newer versions, these techniques will help you gain insight into user logins effectively.

Table of Contents

SQL Server Last Login Date for User

By default, SQL Server does not store the last login date for SQL logins in a readily accessible column. Unlike Windows Active Directory, SQL Server’s system views are like sys.syslogins or sys.server_principals do not maintain last login timestamps.

This limitation means you need to use alternative methods to capture or retrieve this information.

Method 1: Querying sys.dm_exec_sessions

You can get information about currently connected sessions and their login times using the dynamic management view sys.dm_exec_sessions using the below query.

SELECT 
    login_name,
    login_time,
    host_name,
    program_name,
    session_id
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

After executing the above query, I got the expected output as shown in the screenshot below.

Limitations:

Only shows active sessions, not historical logins.
Not suitable for tracking last login over time.

Method 2: Using Default Trace to Find Last Login

SQL Server’s default trace captures various server-level events, including login events. Although it’s limited in retention and detail, you can query it to find recent login history.

How to Query Default Trace for Login Events

We can execute the query below.

DECLARE @tracepath NVARCHAR(260);

SELECT @tracepath = path FROM sys.traces WHERE is_default = 1;

SELECT 
    LoginName,
    HostName,
    ApplicationName,
    StartTime
FROM fn_trace_gettable(@tracepath, DEFAULT)
WHERE EventClass = 14 -- Audit Login
ORDER BY StartTime DESC;

Notes:

EventClass 14 corresponds to login events.
Default trace files are limited in size and rollover, so older data may be lost.

Method 3: Creating a Login Trigger to Track Logins

One proactive method I often recommend is creating a server-level logon trigger that records login events to a custom table.

Step 1: Create a Table to Store Login History

CREATE TABLE dbo.LoginAudit (
    LoginName NVARCHAR(128),
    LoginTime DATETIME DEFAULT GETDATE(),
    HostName NVARCHAR(128),
    ProgramName NVARCHAR(128)
);

After executing the above query, I got the table created successfully, as shown in the screenshot below.

Step 2: Create the Logon Trigger

CREATE TRIGGER trg_LogLogins
ON ALL SERVER
FOR LOGON
AS
BEGIN
    INSERT INTO dbo.LoginAudit (LoginName, HostName, ProgramName)
    VALUES (
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        PROGRAM_NAME()
    );
END;

After executing the above query, I got the trigger created successfully, as shown in the screenshot below.

Advantages:

Real-time tracking of all login events.
Customizable to capture additional info.

Considerations:

Test thoroughly to avoid performance impacts.
Ensure proper permissions.

Frequently Asked Questions (FAQs)

1. Does SQL Server store the last login date by default?

No. SQL Server does not maintain last login timestamps by default for SQL logins.

2. Can I get the last login date for Windows-authenticated users?

You can track Windows login events via Windows Event Logs or integrate with Active Directory auditing.

3. Will logon triggers affect server performance?

If designed efficiently and tested, logon triggers have minimal impact, but excessive logic can slow down logins.

4. How long does default trace data last?

Default trace files rollover and typically store data for a few days, depending on server activity.

Conclusion

Tracking the last login date for users in SQL Server is an essential task due to SQL Server’s default behavior. However, by using methods like querying the default trace, using dynamic management views, setting up audits or extended events, or implementing custom logon triggers, you can get detailed insights into user login activity.

You may also like the following articles.

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.