Vryjdfkk

When I run this blob of SQL on SQL Server 2016 or an Azure SQL DB:

--hello world

SELECT      event_info

FROM        sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)

WHERE       req.session_id = @@SPID

I get this error, followed by the expected results:

Msg 300, Level 14, State 1, Line 5

VIEW SERVER STATE permission was denied on object 'server', database 'master'.

Msg 297, Level 16, State 1, Line 5

The user does not have permission to perform this action.

--hello world

SELECT      event_info

FROM        sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)

WHERE       req.session_id = @@SPID

The documentation for sys.dm_exec_input_buffer states (emphasis mine):

On SQL Server, if the user has VIEW SERVER STATE permission, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

Thus, according to the documentation, I am allowed to access this data. Why, then, is the error being raised?

Using the condition WHERE req.session_id = <some value> triggers the error condition. Then the line is ignored, and the rest runs correctly. Try executing:

SELECT      event_info

FROM        sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)

without the WHERE clause, and you should get the correct output.

tl;dr Looking for a specific session_id triggers the error.

Welcome, Taudris! My feeling is that it depends on what wackiness is is happening in SQL Azure, and how your permissions were set up, and how you are logged in. I have also noticed that if i run things when I am NOT in master, I don't get errors, but if i run them in the context of master, I do (even if I'm referring to a system view or function only available in master).

Additionally, if i connect using the sqllogin which is sa, I can run things which I cannot under my windows account which is supposed to have super rights, and vice versa.

It seems that setting up a DBA (at least at our company) with the appropriate limited but usable rights can be a challange. I actually didn't run into the issue you mentioned above, but I've run into other quirky behavior.

this does the same thing. Get the result but also a permission denied error
select event_info FROM sys.dm_exec_input_buffer(@@spid, NULL)

seems like a bug since the documentation says you should be able to view the info for the current spid. Should not get an error.
I found out the hard way. Put this in a proc to retrieve event_info. The proc ran and completed. The web client app got the error message.