HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Permission error when using sys.dm_exec_input_buffer with @@SPID

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorwithpermissiondm_exec_input_buffersysusingspidwhen

Problem

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?

Solution

This is now documented:

Text:

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.

ℹ Important

Running this DMV outside of SQL Server Management Studio against SQL
Server without VIEW SERVER STATE permissions (such as in a trigger,
stored procedure, or function) throws a permission error on the master
database.

On SQL Database, if the user is the database owner, the user will see
all executing sessions on the SQL Database; otherwise, the user will
see only the current session.

ℹ Important

Running this DMV outside of SQL Server Management Studio against Azure
SQL Database without owner permissions (such as in a trigger, stored
procedure, or function) throws a permission error on the master
database.

Context

StackExchange Database Administrators Q#218540, answer score: 2

Revisions (0)

No revisions yet.