Why Additional EXECUTE Permission many be Required to Generate Execution Plans for Stored Procedures (4379317)

This article explains why the default SQL Server Foglight permissions script does not grant general EXECUTE access to user-defined stored procedures and what to do about it.

This is relevant when users can view performance data in SQL PI but cannot generate execution plans for stored procedures directly in SQL PI. 

When a user runs the standard Foglight SQL Server Permissions Grant Script, it enables SQL PI to monitor query activity by granting access to necessary system views and procedures. However, it does not include general EXECUTE permissions on user-defined stored procedures.

What the SQL Server permissions script Grants

The script grants EXECUTE on a few specific system stored procedures only:

• master.dbo.xp_enumerrorlogs

• master.dbo.xp_readerrorlog

• msdb.dbo.agent_datetime

These permissions are required for Foglight's background data collection — not for running custom or user-defined stored procedures.

Why Execution Plans Can’t Be Generated Without EXECUTE

To generate either an estimated or actual execution plan for a stored procedure, SQL Server needs to:

• Parse the stored procedure.

• Validate its syntax and dependencies.

• Potentially compile and execute it.

These actions require EXECUTE permission on the procedure itself. Without it, SQL Server blocks the request and does not produce a plan.

To allow users (including Foglight monitoring accounts or developers) to generate execution plans for stored procedures, you must manually grant EXECUTE on the specific stored procedures or at the schema level.

Grant on each stored procedure

GRANT EXECUTE ON [dbo].[YourStoredProcedure] TO [YourMonitoringUser];

Grant on the entire schema

GRANT EXECUTE ON SCHEMA::[dbo] TO [YourMonitoringUser];

This is simpler and ensures access to all existing and future procedures in that schema.