There are a few system functions for returning the number of connections but,they are many times misunderstood.

@@CONNECTIONS : Returns login attempts since the server was started.
@@MAX_CONNECTIONS: Returns maximum number of connections supported.
The simplest way to list all users and processes is to use the system stored procedure: sp_who To list all active users: sp_who ‘active’ to list details about a particular user: sp_who ‘login-name’ Now to obtain the current number of connections to the server, you can use:
SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses
And just to get the user connections, omitting the system processes, use: SELECT cntr_value AS User_Connections FROM master..sysperfinfo as pWHERE p.object_name = ‘SQLServer:General Statistics‘ And p.counter_name = ‘User Connections’