Accessing sys.syslockinfo from an application role
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
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
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
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.
