SQL 2005, @@SPID and sysprocess table(view) access

[Dweller]

Hi,

Used to have next functionality in SQL 2000 and earlier:
application signals some data locking to others by storing some Id, connection @@SPID and current time in in some, lets say, Locks user table. And deletes the record when done. Other processes may check the record and if exists - have to validate it (may be just some garbage from broken connections).
Validation happened by checking stored in Locks SPID against those in sysprocesses. Record considered as garbage if the SPID is not in sysprocesses now or connected after time, registered in Locks (means it just reused by some newer connection).
Above was working fine because in SQL2000 and earlier everybody could see all records in sysprocesses. In 2005 user can see only his own record (unless he is member of processadmin server role). Also my users working under Application Role, so I cannot give them processadmin even if I want to. So the question is:

How to get access to all records in sysprocesses for simple
user, working under Application Role?

Please note, SPs with EXECUTE AS clause do not work for the case.

Thanks.

[rmiao]

No one can query system tables directly in sql2k5, try query sys.dm_exec_requests view.

[Dweller]

I would be happy to use sys.dm_exec_requests or sys.dm_exec_sessions - doesn't really matter for me. But problem is still exactly the same: regular user can see only his own record. Any other idea how to validate SPID?

[rmiao]

May try setup sql agent proxy account, associate it to sql id with proper permission, and link your user to that proxy account. Then let user run the query as sql job with proxy account.

[Dweller]

That's even sound complicated
I decided to use ##<SpecialUniqueName> temporary tables with some specific info inside. Such tables visible to everybody and get dropped as soon as login disconnected. Just exactly what I need
Thanks for your help anyway.