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.

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 (http://blog.SQLAuthority.com)

About these ads

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

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

  2. 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.

  3. 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.

  4. 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

  5. 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

  6. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s