How to see active SQL Server connections?

SELECT
    DB_NAME(dbid) as DBName,    
    loginame as LoginName,
    *
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame
;