I just received an email from one of the readers asking for help with error he encountered while attempting to run DMV.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
Fix/Solution/Workaround:
The above error is usually generated when the user who is trying to run the DMV does not have access to the run the DMV. I suggested him to contact his server admin to grant him VIEW SERVER STATE permissions so that he can run the DMV.
Example:
If user does not have VIEW SERVER STATE permissions when he runs any DMV, an error is generated.
When the following script is run by Admin (please note that the user cannot modify its own property in this case), it will give the necessary rights to the user.
GRANTÂ VIEW SERVERÂ STATE TO UserName
GO
After the above script is run on the same DMV, it should not show any error.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Thanks
Thank you!
It has helped me in solving problems with viewing reports of blocked transactions:
Instance (right click) -> Reports-> Standard Report–> All blocking transactions
Thanks…
It was helpfull
Hola,
muchas gracias por la solución al problema. Llevaba ratos tratando de resolver este problema pero ahora ya lo tengo :)
Thanks you. It helped. However one doubt, how can we just provide access to only database level DMVs ? I mean I think for accessing database-scoped DMVs only view database state isrequired. Tell me how it works.
Thanks!!
Maybe not directly related, but need to mention here: I’ve had similar permission issues on Windows, when you are the administrator on Windows you don’t automatically have all rights, or perhaps SQL Server management studio don’t pick up the profile.
When I want to launch Management studio I have to right-click and then “run as administrator” and only if I do this do I have full permissions.
A very simply problem which can turn into a nightmare if you don’t know about this – this might help someone.
Hi Pinal
It is a way to not GRANT VIEW SERVER STATE TO public? I can’t grant
this right for all users (~340) on my server but i need to know login ip-s for logon trigger an table.
Thanks
Hi
Thanks it helped me resolve the issue for service account to create reports.
Thanks Again
KSU
Hi
granted that permission for the user but he still has no right to execute neither sys.dm_os_performance_counters nor sys.dm_db_index_usage_stats. On the db itself he is db_owner
Pinal, is there a way to audit who are using the DMV’s on a server? Basically I need to a way to audit whoever has been granted View Server State as it does not seem like a good audit practice to grant this to all users. Now we want to revoke it but without knowing who really is currently using the DMV’s, it becomes difficult. Thanks a lot
MY BIRTH DAY IS JANUARY 18 2010…