I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on the view says that what is returned depends on the user permission:
If the user has VIEW SERVER STATE permission on the server, the user
will see all executing sessions on the instance of SQL Server;
otherwise, the user will see only the current session.
The stored procedure is actually an activation stored procedure of a broker queue:
CREATE QUEUE test_queue
WITH
STATUS = ON,
RETENTION = OFF ,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = test_procedure,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF ),
POISON_MESSAGE_HANDLING (STATUS = ON)
ON [PRIMARY]
When I read the MSDN article, I changed
EXECUTE AS SELF
to
EXECUTE AS 'dbo'
which did not make any difference. sys.dm_exec_requests
would still return a single row.
Also I tried to do
EXECUTE AS OWNER
Which made no difference either. As I understand the stored procedure owner is who created it, which was me. And as I'm in a sysadmin role that should be working, but it does not. I'll appreciate any troubleshooting tips.
In particular I would like to know how to list a server permissions for a given user so I could check if they include 'VIEW SERVER STATE' permission as per the article.
The confusing part is that user as I understand it is on database level, so it's unclear to me how it can have server permission. If you could clarify that, it would be great too.
Best Answer
See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are:
EXECUTE AS
, even if the module is signed, the principal will not have access outside the host database because of how Service Broker executes the activation procedure)