Using SQL Server 2005 you can take help of DMV - sys.dm_exec_connections that will give server-level information about the connections. In the olden days usage of sysprocesses system table used to be heavy and same approach can be achieved by using system DMVs within 2005 version onwards. All these DMVs - sys.dm_exec_connections , sys.dm_exec_sessions , and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table. So if you have any adhoc code that used to get connections...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2008/05/02/4138.aspx