Please help me on schedule refresh error

[YEDOTILOKESH73]

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: The EXECUTE permission was denied on the object 'udf_StripHTML', database 'xxxxxx', schema 'dbo'."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"14"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"xxxxxx;aaaaaaa"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"The EXECUTE permission was denied on the object 'udf_StripHTML', database 'xxxxx', schema 'dbo'."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"229"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Query1.

[gabimuenster]

Hi @YEDOTILOKESH73,

thanks for reaching out. Please let me make sure I understand properly:
With the same database, you mean the SQL database you used as data source for your Power BI dataset?
And with other reports, you mean reports not connected to the dataset, that had refresh issues, but with their own datasets?
If both assumptions are correct, the only explanation I have is that you are using different objects from the same SQL database. Please check if you are using all of the objects you are using in the erronous dataset also in the datasets, that are refreshing successfully.
If one or both of the assumptions are incorrect, please explain in more detail.

[YEDOTILOKESH73]

@gabimuenster Thanks for the reply.

1.I have used SQL query for connecting database to power bi with the credentials. 

2.For other reports also(query based) I have used the same database and credentials to connect which are refreshing succesfully without any error.

*Only 1 report is showing the given error

[Jeanxyz]

Have your added the SQL server to your gateway? If you upload a new pbix report with SQL connection query, you need to add the sql server to the gateway. In the printscreen below, I have loaded a new pbix file, it won't get refreshed because the sql server is not mapped to any data source in my gateway. If you have the same issue, try to add the sql server to your gateway first.

[gabimuenster]

Hi,

the user you are using to access the SQL data source is missing EXECUTE permissions on the user-defined function udf_StripHTML that is used somewhere in the data source artifact. Please make sure you are using the user to authenticate or contact your database admin to grant required permissions.

[YEDOTILOKESH73]

Hi gabimuenster,

I was using same database for other reports which are working fine and refreshing daily.If there is any other solution please suggest,Thank you.

[gabimuenster]

Hi @YEDOTILOKESH73 ,

using the same database is not necessarily leading to an error. The question is, if you are using the same database objects in all the reports. For example, the user defined function might be used in view A in the database, but not in view B. If you would use view A only in the report that fails to refresh, and use view B in all other reports, that would be a logical explanation. Can you please check if there are database objects in only the report that fails refreshing? If so, my assumption is that the function is used in one of those objects, and the sql login still would require execute permissions on that function.