I know that this is a strange requirement but I need to find out database user name(not the login name) from sys.sysprocesses for all the currently running processes. For e.g. select spid,dbid, uid from sysprocesses where uid corresponds to database user in that particular database. So what I want is one more column which can get me database user name from uid. Please let me know if this is possible with just a simple select query.
Logins (on the server) do not automatically correspond to users (in the database). For instance, "sa" does not have its own user in the database, but uses "dbo". This might get you started.
SELECT s.session_id, sp.[type], sp.type_desc, sp.[name] AS loginName, dp.[name] AS userName, sp.is_disabled FROM sys.dm_exec_sessions AS s LEFT JOIN sys.database_principals AS dp ON s.security_id=dp.[sid] LEFT JOIN sys.server_principals AS sp ON s.security_id=sp.[sid];
sysusers, etc are deprecated and will probably stop working in a future version of SQL Server.
I go into a bit more detail on SQL Server security in my blog post.