o provide permission for SHOWPLAN access in an Azure SQL Database, you’ll need to follow these steps. SHOWPLAN allows users to view the execution plans of queries, which is useful for performance tuning.

Step 1: Connect to the Azure SQL Database

Use a tool like SQL Server Management Studio (SSMS) or Azure Data Studio to connect to your Azure SQL Database.

Step 2: Create a SQL User or Role (If Necessary)

If you don’t have a specific user or role in mind to grant SHOWPLAN permission, you can create one.

Example: Create a New SQL User

CREATE USER [MyUser] WITH PASSWORD = 'YourStrongPassword!';

Example: Create a New Role

CREATE ROLE [ShowPlanRole];

Step 3: Grant SHOWPLAN Permission

SHOWPLAN permission can be granted to a specific user or a role. This permission allows the user to access the execution plans of queries.

Example: Grant SHOWPLAN Permission to a User

GRANT SHOWPLAN TO [MyUser];

Example: Grant SHOWPLAN Permission to a Role

GRANT SHOWPLAN TO [ShowPlanRole];

Step 4: Assign the Role to a User (If Using a Role)

If you granted SHOWPLAN permission to a role, you need to assign that role to the specific user.

Example: Assign Role to a User

ALTER ROLE [ShowPlanRole] ADD MEMBER [MyUser];

Step 5: Verify the Permission

To ensure that the permission is granted successfully, you can run the following query to check the granted permissions:

Example: Check Permissions for a User

SELECT 
    pr.name, 
    pe.permission_name, 
    pe.state_desc 
FROM sys.database_permissions pe
JOIN sys.database_principals pr
ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'MyUser';

This query will show the permissions assigned to MyUser, including SHOWPLAN if it was granted successfully.

Step 6: Test the Permission

You can now log in as the user MyUser and run a query to view its execution plan.

Example: View Execution Plan

In SSMS or Azure Data Studio, run the following:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM YourTable;
GO
SET SHOWPLAN_XML OFF;

The output will display the execution plan for the query.

Summary

• Create a user/role (if needed).
• Grant SHOWPLAN permission to the user or role.
• Assign the role to a user (if using roles).
• Verify the granted permissions.
• Test by viewing the execution plan.

Mohammad Gufran Jahangir