cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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
Highlighted
Advocate II
Advocate II

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. 

 

Anyway, I found out that SELECT and WRITE is enough for Patch to work.

View solution in original post

4 REPLIES 4
Highlighted
Advocate II
Advocate II

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. 

 

Anyway, I found out that SELECT and WRITE is enough for Patch to work.

View solution in original post

Highlighted

Hi @serastpan. I'm in the middle of setting up SQL users for my PowerApps for the first time and came across your post. Thank you for the information RE: Minimal SQL perms required for Patching.

 

Can I ask you what type of user you set up? I'm confused by these docs on how to create "Users that authenticate at the database".

 

Do you have any insights into this?

 

Thank you very much for your time.

Highlighted

Sorry for the really late reply (haven't been working for a while in Powerapps).

 

I hope you managed to get everything working. 

 

I setup the users using SQL Server Authentication. 

 

Basically, you'll need to use: 

 

CREATE USER [user]
WITH PASSWORD = N'password';

 

And then give them the permissions: 

 

GRANT SELECT, UPDATE ON SCHEMA::[schema] TO [user];

Highlighted

All good. I was able to get things setup using Contained Users which are supposedly “portable”.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,186)