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

 

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

View solution in original post

4 REPLIES 4
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. 

 

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

View solution in original post

Highlighted
seadude
Level 10

Re: sql Patch function permissions

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.

serastpan
Level: Powered On

Re: sql Patch function permissions

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

seadude
Level 10

Re: sql Patch function permissions

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (7,038)