asked on

Tracking SQL connections for one month

Microsoft SQL 2014:
Best way to Track what users are connecting to SQL server for one month period of time.
I know you can use profiler to do this but need it for a month and once I log off the server it disconnects it.

Not I am not an expert with SQL.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

I also recommend the logon trigger where you can capture the login id and the current date time and store it in a table for later reports. Something like:

CREATE TRIGGER RecordLogon
ON ALL SERVER 
FOR LOGON
AS
BEGIN
     INSERT Login_log  (LogInDateTime, LoginName) 
      VALUES  (GETDATE(), ORIGINAL_LOGIN())
END

Open in new window

Jason clark

Tracking SQL connections

I would like to suggest you to run a server side trace - for your entire business life cycle. There might be cases where some reports are ran end of month or end of quarter.
I found a helpful post after a long search on google you should go through this it may help yours. http://dba.stackexchange.com/questions/54494/sql-server-keep-track-of-all-connections-disconnections

jyoung127

ASKER

Sorry for the delay I will be looking into all of the suggestions earlier next week and report what work for me.

Scott Pletcher

Extended events are great to use, they just take a little getting used to. But they are very powerful and not as much overhead on the system as other methods.