Post reply

Error while querying DMvs

  • December 16, 2009 at 10:58 am

    #141163

    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]

  • December 16, 2009 at 11:04 am

    #1093204

    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

  • December 16, 2009 at 11:05 am

    #1093205

    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]

  • December 16, 2009 at 11:20 am

    #1093216

    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

  • December 16, 2009 at 11:23 am

    #1093221

    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]

  • December 16, 2009 at 11:27 am

    #1093224

    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

  • December 16, 2009 at 1:08 pm

    #1093274

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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