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