Hi!
My team currently has multiple reports connected to our Azsure SQL database. 3 accounts were created to restrict data access to the DB corresponding to different schemas. All reports are currently on 1 workspace.
our problem now arises on scheduled refreshes. the error we are getting is permission denied on an object. we know the accounts are given the appropriate permissions because 1) we have tested the accounts and queries using SSMS and the accounts are working fine, 2) whenever we re-apply the credentials used in the settings of the report and manually trigger the refresh it is successful and 3) same credentials are working when refreshing the report using PowerBI Desktop.
what we have tried so far is remove all scheduled refresh and just manually trigger it once published. however, if the refresh was successful the refresh of other reports using a different schema will fail. the reports will only be successful again if we re-input the credentials. but this will again led the other reports using the other scehamas to fail. this lead us to assume the service is still using the credentials from the previous successful refresh.
I'd like to know the community's thoughts on this and if there is a possible solution we can implement for a succesful scheduled refreshes.
