none
SELECT statements generates "SHOWPLAN permission denied in database ‘DB1'."

    Question

  • We have a domain user with read-only access to database  "DB1". Running 

                 select * from <myschema><table1>

    in Query Analyzer generates this error.


                "SHOWPLAN permission denied in database ‘DB1".

    Any ideas what could be causing this?

    TIA,

    Barkingdog

    Friday, March 30, 2012 11:55 PM

Answers

  • Hi,

    I forgot to add, that if you don't want the user to see the execution plan then you'll need to ensure that the option 'include actual execution plan' is not selected in Management Studio (option is under Query menu option) and set showplan options are not used by the user e.g. SET SHOWPLAN_XML ON.

    • Marked as answer by edm2 Saturday, March 31, 2012 2:19 AM
    Saturday, March 31, 2012 12:37 AM

All replies

  • Hi,

    The read-only user doesn't have sufficient privileges to use SHOWNPLAN.

    In order for them to use it, you'd have to grant the SHOWPLAN permission as shown below where databaseuser is the name of the user in the database.

    USE mydatabase
    GO
    GRANT SHOWPLAN TO databaseuser
    GO
    More info can be found here http://technet.microsoft.com/en-us/library/ms175117(v=sql.105).aspx
    Saturday, March 31, 2012 12:29 AM
  • Hi,

    I forgot to add, that if you don't want the user to see the execution plan then you'll need to ensure that the option 'include actual execution plan' is not selected in Management Studio (option is under Query menu option) and set showplan options are not used by the user e.g. SET SHOWPLAN_XML ON.

    • Marked as answer by edm2 Saturday, March 31, 2012 2:19 AM
    Saturday, March 31, 2012 12:37 AM