Last Login date and time

SQL Server Administration
1

Is there a way to get last login date and time for all users including AD users belonging to an AD group? I'm using SQL 2019.

I found this query but it appears to only show active sessions:

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

Any help is appreciated.

Thanks,

Van

2

Probably the easiest way is to use Logon triggers:

Within the trigger code you'd grab the login name and current time and put them in a table. You can INSERT a login if it doesn't already exist, or UPDATE the existing login with the current time as the last login timestamp.

There's a few other options but involve more effort:

That will write successful logins to the SQL Server error log. You would then have to read that log periodically to extract the successful login items. Any command-line search tool would work, or the sp_readerrorlog stored procedure in SQL Server. The downside of this method is that those messages will bloat your error log, and you have to discard all the non-login error messages, and then finally find the maximum date for each login.

SQL Server's Audit function has a SUCCESSFUL_LOGIN_GROUP category to capture just those events to a file. You would still need to query the audit file and aggregate the latest login date for each login. It's less data to query but still a bit of work.

You can do something similar with extended events:

The extended event type you'd monitor will have a different name, I don't know what it is but you can query the sys.dm_xe_objects DMVs and look for some kind of login event.

Regarding AD groups, you'd have to pull the group membership of each AD login to make that determination, it's not included in the SQL Server login metadata.

1 Like