When trying to execute a SQL recipe we have the error SHOWPLAN permission denied in database

DataIKUAD

When trying to execute a SQL recipe in a dataiku flow, we have the error SHOWPLAN permission denied in database. We tried to set the advanced options for the SQL recipe to not display the execution plan. We tried to uncheck the validation before the run.

Are there any other settings we need to modify to disable the SHOWPLAN for SQL recipes?

The error we have in the logs is

[2024/02/01-15:26:24.590] [FRT-43-FlowRunnable] [INFO] [dku.queries.executionplan] - Compute execution plan
[2024/02/01-15:26:24.593] [FRT-43-FlowRunnable] [WARN] [dku.queries.executionplan] - Computing execution plan inside a transaction
[2024/02/01-15:26:24.670] [FRT-43-FlowRunnable] [WARN] [dku.flow.sql] - Could not print execution plan
com.microsoft.sqlserver.jdbc.SQLServerException: SHOWPLAN permission denied in database 'xxxxxxxx'.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:255)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:920)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:814)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)

Alexandru

Hi,

​This is requirement was introduced in DSS 11.4.1 DSS. We've since removed this requirement in DSS 12.4.2.

https://doc.dataiku.com/dss/latest/release_notes/12.html#datasets-and-connections
> SQLServer: Fixed all-catalogs table listing when credentials do not allow to access some databases

If you are unable to upgrade to 12.4.2 or later, you will need to add showplan permission.
GRANT SHOWPLAN TO user;
https://doc.dataiku.com/dss/latest/connecting/sql/sqlserver.html#requirements

Kind Regards,