Link to home
Start Free TrialLog in
Avatar of Stanley Lai
Stanley LaiFlag for Hong Kong

asked on

How to extract MS SQL accounts with last login date?


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.
Avatar of lcohan
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Stanley Lai


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.