Error while querying DMvs

  • Guys:

    When am querying DMVs in queries like "select * from sys.dm_db_index_usage_stats"

    am getting the following error.

    Msg 297, Level 16, State 1, Line 2

    The user does not have permission to perform this action.

    What kind of access should i have to execute DMVs?

    Thanks

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • NewBeeSQL (12/16/2009)


    What kind of access should i have to execute DMVs?

    Thanks

    You need to have View Server State Permissions


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Thanks....how / where to set the permission?

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • NewBeeSQL (12/16/2009)


    Thanks....how / where to set the permission?

    Choose the Login, right click and select properties, go to Securables page and click Add, select you server, you should see a list of permissions, in which navigate to the bottom, you find View Server State. Select Grant.

    Remember, if you are not given that permissions, then ask your DBA for the same, if it is you personal system you own it so it's you wish. 🙂

    Refer SQL Server Books Online whenever you need such info also have a SQL Server Database Administration handy always.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Thanks Bru..i donot have access to Server. i will ask DBA to set this permission..

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • The following doc would also be a good read:

    http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • You'll also need VIEW DATABASE STATE for some of the DMV's.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply