Tracking SQL connections for one month

jyoung127
jyoung127 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You could use extended events (I think) or a logon trigger.

The logon trigger starts like this:

USE master
GO
CREATE TRIGGER trigger_name
ON ALL SERVER
AFTER LOGON  
AS
SET NOCOUNT ON;
--your logic goes here--
GO --end of trigger
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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 clarkDBA Freelancer

Commented:
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

Author

Commented:
Sorry for the delay I will be looking into all of the suggestions earlier next week and report what work for me.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial