Today I was trying to perform a big "UPDATE" in a SQL SERVER 2005 database between tables from different databases, even when the "UPDATE" works I was was concerned about the large amount of time spent on it's execution.
I've tried a "Display Estimated Execution Plan" (CTRL+L) and showed me the following error:
SHOWPLAN permission denied in database 'BussinessDW'.
After I googled for "GRANTING SHOWPLAN" I found that the following command solves the problem:
GRANT SHOWPLAN TO @user - - @user is the database user to who I want to permit the showplan
However after a little research in MSDN I found that security risks are involved with this sentence. Because "SHOWPLAN" includes "CONTROL" and "ALTER TRACE" permissions, I decided to revoke the "SHOWPLAN" permission to the user after I've completed the analysis of the execution plan.
To revoke the show plan I used the following command:
REVOKE SHOWPLAN TO @user - - @user is the database user to who I want to remove the showplan permission
To learn more about SQL Server permissions, visit Laurentiu Cristofor's blog
P.S. 2014-04-24: Thanks to the "unknown" user who requested the command to revoke the show plan permission, I forgot to put it in the original entry.
