SQL SERVER – DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action

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.

SQL SERVER - DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action dmverror

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.

SQL SERVER - DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action dmverror1

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Get Server Version and Additional Info
Next Post
SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results

Related Posts

12 Comments. Leave new

  • Jayesh Prajapati
    February 22, 2011 12:48 pm

    Thanks

    Reply
  • Artem S. Tsygankov
    April 5, 2011 8:42 pm

    Thank you!
    It has helped me in solving problems with viewing reports of blocked transactions:
    Instance (right click) -> Reports-> Standard Report–> All blocking transactions

    Reply
  • Thanks…

    It was helpfull

    Reply
  • Hola,
    muchas gracias por la solución al problema. Llevaba ratos tratando de resolver este problema pero ahora ya lo tengo :)

    Reply
  • 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.

    Reply
  • Thanks!!

    Reply
  • Christof Coetzee
    November 15, 2011 11:59 am

    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.

    Reply
  • 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

    Reply
  • Hi
    Thanks it helped me resolve the issue for service account to create reports.

    Thanks Again
    KSU

    Reply
  • marc-david.johne@audacon.com
    May 6, 2013 8:18 pm

    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

    Reply
  • 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

    Reply
  • MY BIRTH DAY IS JANUARY 18 2010…

    Reply

Leave a Reply

Menu