sys.dm_exec_input_buffer(@@SPID, NULL) returns "VIEW SERVER STATE permission was denied on object 'server', database 'master'."
Executing the system function sys.dmexecinputbuffer, passing @@SPID and NULL (or 0) for the arguments, within a trigger succeeds in returning the SQL statement that caused the trigger, however, it still throws the error message "VIEW SERVER STATE permission was denied on object 'server', database 'master'."
The documentation for sys.dmexecinputbuffer states that the user will have access to commands issued in their own session if they do not have the "VIEW SERVER STATE" permission, however, since it still throws the error, it basically prevents using this under normal circumstances.
It should not throw this error when attempting to do something that is allowed.
mdell.seradex
shared this idea
2 comments
-
mdell.seradex
commented
It appears that there has been no movement on this as yet.
Note that the documentation was updated to essentially tell people that this function is only useful for users with the "VIEW SERVER STATE" permission, which seems more restrictive than what you had intended it to be.
Also, please recall that using "DBCC INPUTBUFFER(@@SPID)" instead is quite limiting as it cannot return the full SQL for long commands and can only be used in limited situations.I look forward to hearing your feedback.
Thank you
-
Solomon Rutzky
commented
I was able to reproduce this using the following T-SQL, which also shows that DBCC INPUTBUFFER does work correctly in the same context:
---------------------------------------------------
USE [tempdb];
CREATE LOGIN [InpBufTest] WITH PASSWORD = 'Show Me The Buffer!';
CREATE USER [InpBufTest] FOR LOGIN [InpBufTest];EXECUTE AS LOGIN = N'InpBufTest';
SELECT USER, SYSTEM_USER;DBCC INPUTBUFFER(@@SPID); -- works
SELECT * FROM sys.dm_exec_input_buffer(@@SPID, CURRENT_REQUEST_ID()); -- error:
/*
Msg 300, Level 14, State 1, Line XXXXX
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/
REVERT;DROP USER [InpBufTest];
DROP LOGIN [InpBufTest];
---------------------------------------------------Please note that there is a related documentation issue for this bug:
Errors occur when the user does not have VIEW SERVER STATE permission. #2846 ( https://github.com/MicrosoftDocs/sql-docs/issues/2846 ).
Take care,
Solomon....
https://SqlQuantumLeap.com/