debugsqlMinor
Permission error when using sys.dm_exec_input_buffer with @@SPID
Viewed 0 times
errorwithpermissiondm_exec_input_buffersysusingspidwhen
Problem
When I run this blob of SQL on SQL Server 2016 or an Azure SQL DB:
I get this error, followed by the expected results:
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?
--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 = @@SPIDI 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 = @@SPIDThe 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.
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.