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]
Labels:

4 comments:

  1. Anonymous14 June, 2012 10:39

    Nice

    ReplyDelete
  2. Anonymous21 June, 2013 19:29

    Thank you very much

    ReplyDelete
  3. Anonymous23 April, 2014 06:44

    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
  4. for ict 9912 January, 2018 16:26

    great

    ReplyDelete

Subscribe to: Post Comments (Atom)