-- Show users with highest connections
SELECT login_name, session_count
FROM(
	SELECT login_name ,COUNT(session_id) AS session_count
	FROM sys.dm_exec_sessions
	GROUP BY login_name
) t
ORDER BY session_count desc

In SQL Server 2008 you can also find out how many connections have been created to SQL Server since the last time it got restarted:

SELECT @@Connections

• Sql Server connection count
• sql connection count
• Connection count
• dmv user connections
• sql server connections count