Is it possible to have role based security in a PowerApps app when the connection is a SQL Server database? Can you give some users read only permissions and some users read/write permissions on certain tables from the SQL Server connection that are being used in PowerApps?
If so, how would this be done?
Solved! Go to Solution.
Hi agigliotti,
Please consider to use DataSourceInfo function to see if it will work for you, please check more details at here:
https://powerapps.microsoft.com/en-us/tutorials/function-datasourceinfo/
We can use information at the data-source level, for example, to disable or hide Edit and New buttons for users who don't have permissions to edit and create records.
Best regards,
Mabel Mao
Hi agigliotti,
Please consider to use DataSourceInfo function to see if it will work for you, please check more details at here:
https://powerapps.microsoft.com/en-us/tutorials/function-datasourceinfo/
We can use information at the data-source level, for example, to disable or hide Edit and New buttons for users who don't have permissions to edit and create records.
Best regards,
Mabel Mao
Great question. I have this on my list of unanswered as well...
Basically: Can you have multiple Connections per SQL Connector? For general "Roles" in SQL I create a User, a Role, assign Permissions to the Role, then assign the User to the Role. I then use the Users credentials for the PowerApps SQL Connection. This only results in 1 permission level though.
Example:
--Create Contained DB User
CREATE USER powerAppsUser with password = 'superHardPassword'
--Create Role
CREATE ROLE powerApps
--Grant Role the minimal SQL read/write Permissions
EXEC sp_addrolemember N'db_datareader', N'powerAppsUser' GO EXEC sp_addrolemember N'db_datawriter', N'powerAppsUser' GO
--Add User to Role
ALTER ROLE powerApps ADD MEMBER powerAppsUser;
I then Add Data Source and sign into the SQL Connection using these credentials. But this creates only a
single User/Permission level. Maybe in SQL you could give this user access to ONLY certain tables? Then create another User with permissions to other tables and created another SQL Connection (Add Data Source/Login with this other users creds).
How can we choose which connection the user has access to? Views?
Looking further into this, @v-yamao-msft's suggestion has a link to determine if user has permission on a data source. I think weaving this into the the above scenario where multiple users are created and connected to the app could be the solution. I'll try messing with it and report back on what I find (as time allows 🙂 ).
Wow, Ok. It does not appear that you can specify different SQL Permissions in PowerApps. At least not that I can see. I spent ~4hours testing today.
What I did:
CREATE user1 WITH PASSWORD = 'powerTest1'; GRANT INSERT, SELECT, UPDATE on table1 TO user1; GRANT INSERT, SELECT, UPDATE on table2 TO user1
I'm no SQL expert by any means so take this research with a grain of salt. Hope it helps further the discussion and I look forward to hearing how others are handling this.
Feels like the SQL Connector needs a way to see who (Active Directory) is logged into the app then translate this to SQL Permissions. I don't think this is currently available.
I am also exactly in the same boat! Have you found a workaround for this scenario. How did you ended up solving this case
User | Count |
---|---|
259 | |
111 | |
97 | |
48 | |
41 |