How to display active connections and running queries in SQL Server?

This query shows all queries executed at the moment, the session id, status, used cpu time and the execution duration

SELECT st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

The following query shows the number of connections a user has with a database

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame