3

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?

0

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.

  • Interesting line of thinking, but unfortunately, this didn't work. In SQL Server, I still get the behavior I described in my question. In Azure SQL DB, sys.dm_exec_requests returns more than just my session's requests (which contradicts the documentation), and I get an error for each row, including the one for my own session. It does not show me the input buffer for sessions other than my own when I use OUTER APPLY. – Taudris Sep 25 '18 at 21:48
0

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.

0

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)

It seems like a bug since the documentation says you should be able to view the info for the current spid. It 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.

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.