There are two easy ways to do this. The SQL Server, have two procedures, sp_who and sp_who2 that solves this problem. Syntax:
sp_who 'loginname'
The sp_who procedure shows information about the actual users, sessions and process in a instance of SQL Server. Can be executed without parameters, and returns info about all users and sessions and processes.
The sp_who2 procedure show the same information that sp_who shows (and works the same way too), but adds the cpu time and disk io consumption on the result.
Another way is use the query below, you can retrieve the same information (in less detail) from the sysprocesses system view.
SELECT DB_NAME(dbid) as [database]
[login_time],
[open_tran],
[nt_username]
[hostname],
[program_name]
FROM master..sysProcesses
