Power Platform Community Forum Thread Details

TLDR: Is it possible to query a SQL table in Power Apps where you are not given SELECT permissions to ALL columns in the table?

Hello,

I have a Power App that I built and I'm now working with a DBA to secure the SQL Server connections from my personal account to a service account with limited access.

I provided the DBA with a list of the tables/views and columns I need access to. Internally within my App, I'm even using ShowColumns to limit to those columns. The DBA provided me with a limited access Service account that has permission to the column fields I requested.

However when I change the connection, I get errors related to querying columns that I don't need. One example error is: "Error when trying to retrieve data from the network: Microsoft SQL: The SELECT permission was denied on column 'Department' of the object ..."

I get an error like this for every column it cannot access. I have "Explicit column selection" enabled in the Settings but this has no bearing (on or off).

I even tried rewriting my queries to move the ShowColumns inside the Filter:

From this:

Set(gblRootUser,
 First(
 AddColumns(
 ShowColumns(
 Filter(ADusers, Email = User().Email),
 "SAMAccountName", "FirstName", "LastName", "Title", "Email", "EmployeeNumber", "EmployeeID"
 ),
 "FullName",
 Concatenate(FirstName, " ", LastName)
 )
 ) 
);

To this:

Set(gblRootUser,
 First(
 AddColumns(
 Filter(
 ShowColumns(
 ADusers,
 "SAMAccountName", "FirstName", "LastName", "Title", "Email", "EmployeeNumber", "EmployeeID"
 ),
 Email = User().Email
 ),
 "FullName",
 Concatenate(FirstName, " ", LastName)
 )
 )
);

Is there no way to have Power Apps query a table with limited SELECT permissions?

Thanks

Categories:

Building Power Apps