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

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;

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

  1. 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.

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *