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?

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
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
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

ASKER

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