SQL Server 2016 - How to get last logged in date for user?

Question

How can I get the last logged in date for certain user. I googled and stumbled upon this query

SELECT name, accdate FROM sys.syslogins

But the accdate column seems to be deprecated, and does not update.

I have another clue

SELECT login_name, max(login_time) as last_logged_in 
FROM sys.dm_exec_sessions GROUP BY login_name

But it shows me results only for system users, not the ones which I created with this query

CREATE USER test1 FOR LOGIN test1 WITH DEFAULT_SCHEMA = 'test1'

The question is, how to make custom created users appear in sys.dm_exec_sessions, or what's the alternative to that solution?

Answer 1

Use sys.dm_exec_sessions system view That

shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

Here’s a little script hopes help you out!

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

UPDATE

And About New logins, You Must Login with them firstly for getting a record into sys.dm_exec_sessions

so use the next code for creating a login:-

CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCDa@12'
GO
CREATE USER [NewAdminName] FOR LOGIN [NewAdminName]
EXEC sp_addrolemember N'db_owner', N'NewAdminName'

Now Login by:-

User Name = NewAdminName

Password: ABCDa@12

After Logging successfully, the information of this login is stored into sys.dm_exec_sessions

Answer 2

select DISTINCT login_time, host_name, program_name, login_name from
(SELECT sys.dm_exec_sessions.*,
RANK() OVER(PARTITION BY login_name ORDER BY login_time DESC) as rnk
FROM sys.dm_exec_sessions) l
where l.rnk = 1
ORDER BY l.login_time DESC

Answer 3

By default the last login date isn't recorded by SQL Server :'(

There's a variety of ways you can record it yourself, e.g.

• Extended Events session to record logins (e.g. see this article), then you query that to get what you want. There's a "Connection Tracking" template that probably records the info you need.
• Triggers (ugh, not recommended)
• SQL Server Audit feature (which like Extended Events is super flexible but hard to get your head around when all you want is the last login date).
• A SQL Agent Job that periodically looks at the current logins by querying sys.dm_exec_sessions system view. I wouldn't recommend this since it's highly possible you'll miss some logins, plus it puts a higher load than using the extended events infrastructure that SQL provides.