sql Patch function permissions

(0) Share

Report

Posted on by serastpan

105

Hi, I have created an app that takes some data from Azure SQL and then make something and send it back to Azure SQL.

A user was set up to create the connection to Azure SQL with permissions to ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT and UPDATE the tables under a certain schema.

I have connected my app to Azure SQL using that user and it can retrieve the data easily but when I use the function Patch an exception is thrown saying that there is a poblem in the server "An error in the server occurred". Checking what is being sent to SQL everything seems right and when I changed the connection to another table I have full access, the Patch function works.

I am wondering if I am missing some more permissions my user should have. Which are the permissions Patch function needs to be executed in Azure SQL?

Categories:

Using connectors

I have the same question (0)

All responses (3)

Answers (1)

v-xida-msft on at

Like (0)

Report
Copy link

Link copied!

Hi @serastpan,

Could you please share more details about the error message?

Could you please share a bit more about the Patch function within your PowerApps app?

I have made a test on my side and don't have the issue that you mentioned. The Patch function works well within my PowerApps app. Please consider check if there is a Primary key defined in your SQL table. If you want to create new records or update records in your SQL table using PowerApps, a Primary key is required within the SQL table.

In addition, you could also take a try to add new records into your SQL table using SubmitForm fucntion, then check the if the issue still exists.

Please check and see if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Collect-Patch-functions-do-not-allow-me-to-use-my-SQL-table-as/td-p/93547

More details about Known issues and Limitations of SQL Server connector, please check the following article:

Known Isuues and Limitations of SQL Server connector

Best regards,

Kris

Was this reply helpful? Yes No
serastpan 105 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft,

My issue is that the exception is not clear at all, it just says "An error occurred on the server.".

Let me explain me better. I built an app that was uploading some data to a table in SQL. The connection to SQL is created through a user that has the permissions I mentioned earlier. When I tried to upload the data, I got that exception. The table has a primary key set up.

I tried then changing the connection to another table to whom the user was a db owner and the data was uploaded successfully (exactly same table with same columns, columns types and primary keys).

I am wondering now which are the permissions I am missing in the first connection (through the user that can ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT and UPDATE) so that I can send the data. It is clear that if the user is a db owner then it can send the data without any issues.

So I am wondering, which are the permissions a user needs to upload data to sql through the Patch function?

Was this reply helpful? Yes No
Verified answer

serastpan 105 on at

Like (0)

Report
Copy link

Link copied!

Ok, it seems there was something wrong in the connection. I dropped the connection and recreated it and now it's working properly. Though it said "An error occurred in the server.", the error was actually due to the connection to SQL.

Was this reply helpful? Yes No