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;
30 Replies to “How to Find Last Login Date of a SQL Server Login?”
last connectivity date and userid of database since a year ago
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.
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?
is there a way to find out from where (such as PC or l laptop) did the user logged in sql db?
Try using the “host_name” column in the dmv. That should help!
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.
some one is deleted on login. How will we get whose deleted login and what time.
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.
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.
Thank you very much Mohammad Darab
Just what I needed!
Useful tip, many thanks!