Friday, October 28, 2005 3:42 PM
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
Friday, October 28, 2005 5:22 PMModeratorHere 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.
Monday, October 31, 2005 4:42 PM
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?
Monday, October 31, 2005 6:26 PMModeratorThanks 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.
Wednesday, November 02, 2005 3:42 PMTheir 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.
Wednesday, November 02, 2005 4:59 PMNevermind, I think there was just something wrong with my install. I reinstalled, and now it is working. Thanks again for your help.