Apart from enabling the Audit on the SQL server, is there any other way we could find out the last activity/access date and date of expiration for all SQL Server User login ? Also using DMVs wont work as the information provided by them are limited.
For logins, only things that are like server audits.
- A SQL trace to capture logins. This is more intensive than using an audit though.
- An extended event session to capture the logins.
- You could cobble up a login trigger to record logins to a table, but this is probably the worst of them all.
For expiries you can use Loginproperty('login_name', 'DaysUntilExpiration') but I think this is for SQL logins only. You may need to run a separate PowerShell script to determine the same for AD logins.
Neither of these will help when it comes to an AD account access through an AD group. For those it's much more complicated, either through impersonating each login and selecting the list of activate groups from sys.login_token and recording it all to a table; or cycling through AD with PowerShell again.