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;