SQL script to check date of last login of user to SQL server based on condition

Hello, I would like to know the date/time when user has connected SQL server last time. In my case, I do not have individual user's login created in SQL server, there are AD groups created under logins and all users are mapped to that AD groups. Now I want script to check the user id inside the AD group and show us the result. For example, Suppose there are 2 users - User A and User B inside 1 AD group and User A is connecting SQL regularly but User B has not connected the SQL server from last lets say 100 days so script should check based on this condition and give us the information of User B id. Can someone please help me on this in making custom script.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Oct 25, 2016 at 02:11 AM

sql won't do this 'out of the box' you'll have to record logins as they happen then inspect the record to determine the last login time for a login. A few ways to do this come right to mind login trigger sql audit record successful and failed logins (sql configuration setting - this could have a huge impact on your sql logs)

0 ·