Thursday, April 28, 2011

"SHOWPLAN permission denied" in Database Engine Tuning Advisor

I recently ran into an issue while trying to tune a database used by an ASP.NET website. The error displayed in the Database Engine Tuning Advisor was:

[Microsoft][SQL Server Native Client 10.0][SQL Server]SHOWPLAN permission denied in database 'database'.

After a lot of digging, I managed to track down an obscure post that mentioned DTA runs statements as the user who originally executed the statement - in this case the site's application pool user. This user is locked down and does not have SHOWPLAN permission, even though I, as administrator do have permission.

The solution in the development database (where security is not a major concern) was to grant SHOWPLAN permission to the application pool user:

grant showplan to [domain\user]

4 comments:

  1. Thank you very much

    ReplyDelete
  2. ha! thank you! thank you! thank you! I've been wresting with this issue for hours and needed it solved before end of day.
    Of all the suggestions and info out there, you were the only one I found that had the solution to my problem.

    ReplyDelete