I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to understand the performance of individual queries and procedures.
What risks are inherent on granting this permission? Is there a legitimate justification for this restriction on a development copy of the database?
Note: This SQL IT group has 200+ databases under a single SQL Server instance. Thanks.
Answer: I am taking the lack of response to mean that there are no significant security risks, other than the one noted below. Basically, restricting this on a development database is counterproductive.
I will update this if anyone comes up with a better answer. Thanks for your comments!
Best Answer
Take a look at Showplan Security in Books Online, which says:
This risk does seem negligible, especially given this is a development server, where all queries would be from devs and IT people, not users.