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
    STATUS = ON,
    PROCEDURE_NAME = test_procedure,

When I read the MSDN article, I changed




which did not make any difference. sys.dm_exec_requests would still return a single row.

Also I tried to do


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.

  • 2
    The wording in the documentation is WRONG. VIEW SERVER STATE applies to a server-level login, not a database user. May 21, 2013 at 2:22
  • Oh and now that we know that service broker is involved, please see this - it seems that activation procedures have a bit of a problem in impersonating / elevating privileges. May 21, 2013 at 2:25
  • And while it's obviously not binding or official, please see the comment I added to the page you reference. May 21, 2013 at 2:33

2 Answers 2


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
  • Excuse me, but... how can I create a certificate with a private key in your app database? Thanks.
    – VansFannel
    Apr 11, 2014 at 11:16
  • 1
    @VansFannel please ask that as a standalone question Apr 11, 2014 at 11:20
  • 1
    Question asked here: dba.stackexchange.com/questions/63001/…
    – VansFannel
    Apr 11, 2014 at 11:22
  • Note that the EXECUTE AS doesn't need to be owner either per se, it can even be the same user that was already set to run the activation procedure in the ALTER QUEUE statement, the important part is the EXECUTE AS clause in the procedure itself overrides the context that is otherwise set by the internal activation machinery
    – Mark Sowul
    Jun 4, 2019 at 17:27

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.