SQL Server is designed (along with most db engines) with security in mind the main areas you'll look at for viewing users are
sp_who [spid]|[login]
sp_who2 [spid]|[login]
select * from sys.sysprocesses
All of these commands are accessible for all users (all are views) but the views are restricted to just your current spid unless you have the permission 'view server state' which can be granted on a database by database level.
A user is granted the basic permissions to view their own connection as it is information about yourself (Note that this will always report back that you are running a select command as that's what you're doing at the time)
If you have the 'view server state' permission then you can run a query such as:
select * from sys.sysprocesses where loginame = current_user
(Or create a procedure to run that)
NOTE: this will NOT work for any sysadmin account as their current_user is always 'dbo'
EDIT: Warning, view server state will grant permission to view anyone who is connected, not just people with the current user name so you may want to check around what potential additional security you wish to put in place for that if need be