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

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 i
f
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 sysprocesse
s
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 (unles
s
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.
Also all above is true for sys.dm_exec_sessions and  sys.dm_exec_requests
views.

Thanks.

Have you tried

GRANT VIEW SERVER STATE TO user

Tim S

> Have you  tried

> GRANT VIEW SERVER STATE TO user

Yes I did. Problem is my users work from under Application Role, so the
right is gone as soon as SP_SETAPPROLE is called.

Any other idea? Basically I need to detect somehow that some particular
connection is still alive. May be you know other ways?

Thanks.