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,

krbee

https://community.dataiku.com/discussion/comment/40524#Comment_40524

I am getting this as well in 14.2.1. Was there a regression?

Alexandru

Hi @krbee
Not that we are aware, please raise a support ticket with the job diagnostics if you are seeing issues related to SHOWPLAN permissions.

Thanks

Alexandru

Hi,

After review the SHOWPLAN permission is now required for DSS versions starting 11.4+, after the 12+ release the documentation was updated to reflect this requirement :
https://doc.dataiku.com/dss/12/connecting/sql/sqlserver.html#requirements
https://doc.dataiku.com/dss/13/connecting/sql/sqlserver.html#requirements

https://doc.dataiku.com/dss/14/connecting/sql/sqlserver.html#requirements

Thanks