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.
1
-
Did you checked the default trace. If the activity is was recent you could be lucky.– ShankyJun 20, 2016 at 10:27
Add a comment
|
1 Answer
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.
