Accessing sys.syslockinfo from an application role

Question

In SQL Server 2005, you must have the VIEW SERVER STATE permission in order to access sys.syslockinfo (http://msdn2.microsoft.com/en-us/library/ms189497).

It seems that the VIEW SERVER STATE permission can only be granted to users.

Once you execute sp_setapprole, the connection loses the permissions of the user and assumes the permissions of the application role (http://msdn2.microsoft.com/en-us/library/ms190998).


So, how can I access the sys.syslockinfo view while using an application role?



Note: I have to maintain compatibility with SQL Server 2000

Answer 1

Here are two ways to allow an application role access to syslockinfo:

a) Create a signed stored procedure that exposes syslockinfo and grant execute on it to the application role. An example of procedure signing has been presented in the following post:

controlling security through stored procedures -- 2005 behaviour

b) Grant VIEW SERVER STATE to public.

Thanks
Laurentiu

Answer 2

Thanks for your help.  I see that they just added this knowledge base article: http://support.microsoft.com/kb/906549, which has another example.  Did you write the kb article?

Answer 3

Thanks for posting the KB link, this is recent indeed. No, I didn't write that article but I see it's a complete description of the solution I mentioned as (a). Let us know if you have any trouble with that solution.

Thanks
Laurentiu

Answer 4

Their example works great for syslogins and sysprocesses.  However, it doesn't work for syslockinfo.

This is the error message:
The user does not have permission to perform this action.

Answer 5

Nevermind, I think there was just something wrong with my install.  I reinstalled, and now it is working.  Thanks again for your help.