This script reports gives a list of current users connected via email. It also reports on any linked server connections lasting for more than 1 hour or 60 minutes. Any linked server connecting for more than an hour could potentially be a hung connection and will interfere with database maintenance processes.
Report on existing user connections
--// By Tung Dang 2014/10/22 --// Send Out Notificaton EXEC msdb.dbo.sp_send_dbmail @recipients = 'EMAIL' , @subject = 'User Connection Report for [SERVERNAME]' , @query = 'SELECT SPID , LOGINAME = cast(LOGINAME as varchar(16)) , DB = (SELECT cast(name as varchar(16)) FROM master.dbo.sysdatabases WHERE dbid = sysprocesses.dbid) , CPU , PHYSICAL_IO , LAST_BATCH FROM sysprocesses WHERE (loginame LIKE ''User%'' OR (loginame = ''LinkServer'' and datediff(minute, last_batch, getdate()) > 60 )) AND loginame NOT LIKE ''IgnoreUser%'' ORDER BY nt_username' , @query_result_header = 1 --, @query_result_width = 36 --, @query_result_separator = ',' ;