We help IT Professionals succeed at work.

How to extract MS SQL accounts with last login date?

Stanley Lai
Stanley Lai asked
on
Hi,

Our auditors request us to  create a daily list of all "active" SQL accounts (include all those NT Service, NT Authority, ##MS_Policy together with all accounts via Windows Authentication) with last login date shown.  In case that enabled account never used to login, the last login date should show blank rather than skip it.  May I know how to create such stored procedure so that I can schedule to run it on a daily basis?

Thanks a lot.
Stanley
Comment
Watch Question

Database Analyst
Commented:
You can get a list like that fairly easy by running a SQL script like enclosed below however....this will show you accurate info from the last SQL Server restart as these DMV's are reset/refreshed every time SQL restarts so if you don't have your own custom login audit using a server trace or Login Server Triggers I'm afraid that's all you can do from SQL side:

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

Open in new window

Author

Commented:
Hi Icohan,

 

Thanks a lot for your prompt reply.

 

However, after I execute your provided script, I found it only list out all IDs which once login (with last login date).  For those enabled accounts which never login, it will missed out.

 

May I know how to cater for those missing items?

 

Attached is the result after executed your provided script and direct compare with all those enabled accounts as appeared in the SQL server management studio.  All those marked in yellow are missing items.

 

Kindly please help.

 

Cheers

Stanley
IMG_20200123_151927.jpg