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

Author

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

Dweller

2005-09-28, 8:23 pm

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.
Also all above is true for sys.dm_exec_sessions and sys.dm_exec_requests
views.

Thanks.

Tim

2005-09-28, 8:23 pm

Have you tried

GRANT VIEW SERVER STATE TO user

Tim S

Dweller

2005-09-29, 1:23 pm

> 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.

Sponsored Links