How to Find Last Login Date of a SQL Server Login?

Feel free to watch the how-to video above or read below.

There are many options to find the last login date for a a SQL Server login. Even though there are awesome scripts like Adam Machanic’s “Who is Active” (download link here), sometimes you might find yourself without internet access, or perhaps at a client site that doesn’t have “Who is Active” installed and you forgot your thumb drive at home. :)

You can easily query the sys.dm_exec_sessions dmv to get the last login time of SQL Server logins. Per MSDN, the sys.dm_exec_sessions DMV,

Returns one row per authenticated session on SQL Server….it’s a server-scope view that shows information about all active user connections and internal tasks

Here’s a little script to help you out!

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name;

19 Replies to “How to Find Last Login Date of a SQL Server Login?”

  1. Article is titled “How to Find Last Login Date of a SQL Server Login”. Solution says “shows information about all active user connections”. This does not tell me the last time a login was used for a login whose session is no longer active.

  2. It behaves strangely (SQL 2017). When I log off from SSMS it still returns that login, but when I close my desktop application record immediately disappears. What could be the issue?

    1. Is it the same user that keeps getting deleted or any SQL login? Maybe there’s a trigger that’s deleting the login? Try setting up audit to track that. Once you know what’s dropping the login then you can take corrective action.

  3. excellent… can somebody tell me for how long DMV sys.dm_exec_sessions keeps the login exec sessions? As far I can see I can not find some logins which has not been used for a while.

    thank you

    1. The exec session dmv…only captures live sessions. So whenever you query it, it brings back current active sessions…not historical sessions that aren’t active. So you will not find sessions that are not active on server at the time you ran the query. Hope that makes sense.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.