SQL Server - Required Permissions For Executing Stored Procedures With SHOWPLAN ON

Question

In order to get a stored procedure plan I'm running the following code:

set showplan_all on
exec sp_name param
set showplan_all off

Now I'm wondering if there's any way of doing that without granting execute permissions to the user running this code. After all, this code does not really execute the procedure but rather display it's plan.

Answer 1

The user must have permission to run the stored procedure at the minimum to get the showplan information. You can just grant the user access to run the one stored procedure and not all of them.

GRANT EXECUTE ON sp_name TO username;