Search code examples
sql-serversql-server-administration

Active list of users in SQL Server SQL Database

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
Solution

  • 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.