How to capture first & last active connection per user?

Any suggestions on how to create a report to show when someone first connected to SQL Server and when they last processed anything on SQL Server?

I wrote a query to check sysprocesses every 10 minutes, but it only reports that the person has a connection. It does not tell me if the connection is active. I thought I may need to look at processor and disk to see if those numbers change, but I'm not sure if that is the best approach. Any thoughts?

Thanks, Dave

You can query master..sysprocesses and look at login_time and last_batch columns.

Thanks. Last_Batch will help, but I believe it only records the time a stored procedure was last executed. Since most of the code utilizes stored procs this should help, but I was kinda hoping to find a catch-all solution.

Thanks again, Dave

Last_Batch time is the last time any sql statement is sent from the client to server.

I think BOL is not correct on saying only stored procedure and EXECUTE statement time is captured, you can verify this by running this

select login_time, last_batch from sysprocesses where spid=@@spid

to display last time you executed anything.

Thanks for the info about BOL. I've never tried to verify their statements, just assumed they were correct. I just ran a quick test running a dynamic query of sysobjects and sure enough the timestamp changed.

Dave