Server permissions of an activation stored procedure of a Server Broker queue

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.

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:

• inspect the procedure code to ensure that you trust it

• change the procedure to have an EXECUTE AS OWNER clause (without 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)

• create a certificate with a private key in your app database

• sign the procedure with the private key of the certificate you created

• drop the private key of the certificate (to prevent it from ever being used again)

• copy the certificate into the master database

• create a login from the certificate

• grant AUTHENTICATE SERVER to the certificate derived login

• grant any additional privilege required by the procedure (e.g. VIEW SERVER STATE) to the certificate derived login

StackExchange Database Administrators Q#42668, answer score: 6