cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
serastpan
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
serastpan
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
serastpan
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

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.

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];

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,407)