In SQL server 2005 if you want to check all SQL server sessions following is the command for viewing this;
1 . select * from sys.dm_exec_sessions
But the above command will show the user sessions and as well as the system sessions, if you want to only see the user connections then following is the command;
2. select * from sys.dm_exec_connections
you can also get the session id from the below command and match it with the session_id column of the 2 or 1 command for viewing the specific session information.
3. Select @@SPID