Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : wqba3/9+y/yIKfboY4v/Bf
Power Platform Community / Forums / Power Automate - Using Connectors / sql Patch function per...
Power Automate - Using Connectors
Answered

sql Patch function permissions

Like (0) ShareShare
ReportReport
Posted on 3 Apr 2018 07:49:24 by serastpan 103

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?

  • Verified answer
    serastpan Profile Picture
    serastpan 103 on 04 Apr 2018 at 15:09:09
    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.

  • serastpan Profile Picture
    serastpan 103 on 04 Apr 2018 at 07:33:08
    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?

  • v-xida-msft Profile Picture
    v-xida-msft on 04 Apr 2018 at 01:42:28
    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-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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,342 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans