Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
The wording in the documentation is WRONG. VIEW SERVER STATE applies to a server-level login, not a database user. –  Aaron Bertrand May 21 '13 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. –  Aaron Bertrand May 21 '13 at 2:25
    
And while it's obviously not binding or official, please see the comment I added to the page you reference‌​. –  Aaron Bertrand May 21 '13 at 2:33
add comment

2 Answers

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
  • 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
share|improve this answer
    
Excuse me, but... how can I create a certificate with a private key in your app database? Thanks. –  VansFannel Apr 11 at 11:16
    
@VansFannel please ask that as a standalone question –  Remus Rusanu Apr 11 at 11:20
    
Question asked here: dba.stackexchange.com/questions/63001/… –  VansFannel Apr 11 at 11:22
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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