How do I get as mch information about all active sessions as possible? For example, at any point in time, I want to check who is logged in the database? Also, want to really get all the session information to cover cases where you have the same user logged in twice in different sessions?
The easiest way is to use the system stored procedure sp_who2. If you want more information, you will most likely have to delve into the dynamic management view sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_tran_locks.
Management Studio should also give a view of the current connections. Just not sure where it is in there.