How to Grant Show Plan Privilege

Showplan Privilege it’s granted for any one need to see the execution plane for SQL Server query to check the performance of the index or doing index analysis.

Grant Showplan for one user in one database :

GRANT Showplan TO [DominName\username]

Grant Showplan for one user in All databases in one SQL instance:

EXEC sp_MSforeachdb N’IF EXISTS
(
SELECT 1 FROM sys.databases WHERE name = ”?”
AND Is_read_only <> 1
)
BEGIN
print ”Use [?]; GRANT Showplan TO [DominName\username]”
END’;

after the execution take the Print scripts and run it in another session.

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/ View all posts by Mustafa EL-Masry