Is there a way to get active list of users in a SQL Server database, lets say active from last one month OR say people who connected to the database anytime in last 30 days?
I tried below query but it gives me list of all the users irrespective of whether they are active or not.
SELECT * FROM sysusers
It seems like you want to create an audit trail. There are multiple ways of doing this.
Firstly, you could enable Login Auditing for both failed and successful logins. The default is "Failed logins only.
You'll need to restart the SQL Service for this to take effect. All login attempts will be made to logged to the error log.
The second way is to use SQL Server Auditing
The third way, is the use of Logon Triggers - these can lead to issues with logins in general, so be careful if you implement them.