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?
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With