GRANT SHOWPLAN to many DB into many instances

You could make these arrangements in the model database, and then they would apply to all databases you create on the instance. However, that does not help if you restore databases from other environments where you don't want to grant this permission. But you could write a restore script (possibly in PowerShell that beside restoring the databases, changes the owner (best practice in general) and add this role.

Hi @Alen Cappelletti ,

To do this, you can follow these steps:

Connect to your SQL Server instance using a login with sysadmin privileges.

Create a new server-level role using the following command:

CREATE SERVER ROLE [server_showplan];

Grant the SHOWPLAN permission to the server-level role using the following command:

GRANT SHOWPLAN TO [server_showplan];

Add the developers to the server-level role using the following command:

ALTER SERVER ROLE [server_showplan] ADD MEMBER [domain\SqlDeveloper];

Note that granting the SHOWPLAN permission at the server level allows users to view the query execution plan for any query executed on that server. If you want to restrict this to specific databases, you can grant the SHOWPLAN permission at the database level instead of the server level. In that case, you would need to run the commands in each database separately.

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".