How to resolve SELECT PERMISSION DENIED error in power BI dataflow

[noshin]

I recently took over a dataflow with my credentials - that is being refreshed weekly and was last refreshed successfully few days ago. Today I added another table to the dataflow and tried to refresh it - it is getting failed due to a pre-existing table in the dataflow which is showing select permission denied error.

The old table in question uses a reference to t04056 table - and this table is working fine when I try to query from it in synapse. I am not sure why the issue is occurring. 

The error message from power query:

Microsoft SQL: The SELECT permission was denied on the object 't04056', database 'tcp-azu0011-ase-sdw-b2bdp-prod', schema 'b2b_stg_flexcab_active_conf_vws'.DetailsReason = DataSource.Error DataSourceKind = SQL DataSourcePath = tcp-azu0011-ase-sql-b2bdp-prod.database.windows.net;tcp-azu0011-ase-sdw-b2bdp-prod Message = The SELECT permission was denied on the object 't04056', database 'tcp-azu0011-ase-sdw-b2bdp-prod', schema 'b2b_stg_flexcab_active_conf_vws'. ErrorCode = -2146232060 Number = 229

Error log from the refresh fail:

Error: Encountered user gateway exception: 'Microsoft SQL: The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingErrorMessage]=Microsoft SQL: The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.ValueError.Class]=14 [Microsoft.Data.Mashup.ValueError.DataSourceKind]=SQL [Microsoft.Data.Mashup.ValueError.DataSourcePath]=tcp-azu0011-ase-sql-b2bdp-prod.database.windows.net;tcp-azu0011-ase-sdw-b2bdp-prod [Microsoft.Data.Mashup.ValueError.ErrorCode]=-2146232060 [Microsoft.Data.Mashup.ValueError.Message]=The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [Microsoft.Data.Mashup.ValueError.Number]=229 [Microsoft.Data.Mashup.ValueError.Reason]=DataSource.Error [InnerType]=MashupValueException Microsoft SQL: The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_MashupDataAccessError [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=240974]/MashupDataAccessValueException[ErrorCode=-2147467259 HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259 HResult=-2147467259] [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=240974]'. RootActivityId = f100f2d6-3d26-4f2e-8fda-c51ab7ada0e3.Param1 = Microsoft SQL: The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingErrorMessage]=Microsoft SQL: The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.ValueError.Class]=14 [Microsoft.Data.Mashup.ValueError.DataSourceKind]=SQL [Microsoft.Data.Mashup.ValueError.DataSourcePath]=tcp-azu0011-ase-sql-b2bdp-prod.database.windows.net;tcp-azu0011-ase-sdw-b2bdp-prod [Microsoft.Data.Mashup.ValueError.ErrorCode]=-2146232060 [Microsoft.Data.Mashup.ValueError.Message]=The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [Microsoft.Data.Mashup.ValueError.Number]=229 [Microsoft.Data.Mashup.ValueError.Reason]=DataSource.Error [InnerType]=MashupValueException Microsoft SQL: The SELECT permission was denied on the object 't04056' database 'tcp-azu0011-ase-sdw-b2bdp-prod' schema 'b2b_stg_flexcab_active_conf_vws'. [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_MashupDataAccessError [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=240974]/MashupDataAccessValueException[ErrorCode=-2147467259 HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259 HResult=-2147467259] [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=240974] Request ID: e760b635-a5c4-0bd6-078d-99322446acca.

Any help would be greatly appreciated.

[Martin_D]

Hi @noshin ,

Do you access the SQL table from Synapse and from this Power BI Dataflow using the same identity (login)? In a dataflow you can use a different identity to access a data source then your personal identity. 
The two solutions I'd assume are:

• Grant access to table t04056 to the identity that is accessing it from Power BI which might be  a different one than from Synapse or
• Use the same identiy as in Synapse (limitations apply, e.g., if you are accessing the SQL Server from Synapse using a service principal or managed identiy)

If you are not sure which identity is used when the dataflow queries the SQL table then you can use SQL Server Profiler and monitor the User Error Message and SQL: BatchCompleted events. Then you'll see which LoginName causes the error and then you can give this login privileges to read the table. Make sure there is no db_denydatareader membership effectivefor that user.

Maybe it'll be sufficient to renew the credentials in Power BI, as described in this post reporting a similar problem: https://community.fabric.microsoft.com/t5/Service/Refresh-failed-and-solved-re-entering-credentials/...

BR

Martin