Server permissions of an activation stored procedure of a Server Broker queue
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
Actually using certificates is the way to go, as Answer 1 in the other thread is showing (SA permissions issues with many nested objects). In your scenario this just cannot work - unless you mark the database a TRUSTWORTHY. That would make your proc run if impersonated to "dbo". - However, I am not a big fan of this, because it opens the whole database to elevation, unless one really knows who is calling what and owning what...