cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
serastpan
Level: Powered On

sql Patch function permissions

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?

1 ACCEPTED SOLUTION

Accepted Solutions
serastpan
Level: Powered On

Re: sql Patch function permissions

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.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: sql Patch function permissions

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-us...

 

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
serastpan
Level: Powered On

Re: sql Patch function permissions

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?

serastpan
Level: Powered On

Re: sql Patch function permissions

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.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (2,710)