cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimNYC
Frequent Visitor

Can't Use SQL View Without Granting SELECT Permission on Underlying Table

Good morning,

 

I'm trying to use a SQL view in my Power App. Everything was going fine as I built and tested the app, but prior to deployment I updated the user in my SQL database to only have the SELECT permission on the specific view used in the Power App. But now I get an error in my app that says the SELECT permission was denied on the table that view looks at. Using the view is an important security measure because it ensures that only the rows that I allow will be accessible via Power Apps. The SQL user I created can access the view just fine with any other application, for example, with SSMS, without having SELECT permission on the table. Is there a way to give Power Apps SELECT permission to views without giving it access to the view's tables?

 

I can't deploy my app if doing so means giving users access to all the data in the table. Even though I can restrict what the app sees with a view, the SQL connection is implicitly shared when I share the app and that would allow other users to build their own apps which can view all the data, right?

 

TimNYC_0-1630512769997.png

In this screenshot the object being referenced is not the view that I have connected to, but rather a table that the view uses.

 

 

Thank you,

 

Tim

1 ACCEPTED SOLUTION

Accepted Solutions
TimNYC
Frequent Visitor

I figured out that this only happens to views that contain both tables and other views. Rewriting the view to include only tables solved the problem.

View solution in original post

1 REPLY 1
TimNYC
Frequent Visitor

I figured out that this only happens to views that contain both tables and other views. Rewriting the view to include only tables solved the problem.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,841)