SQL database selection based on user

[smallpotatoe]

Hello,

I hope everyone doing good.

I have a pass through SQL View query (external server). In powerapps, it's simply dbo.table_view.

The actual pass-thru is currently set as: select * from db1.table

Now let's say I have 2 users.

If user A is using the app, I want the query to run as: select * from db1.table

If user B is using the app, I want the query to run as: select * from db2.table

The tables are exactly the same, I just want server selection based on user (I am retrieving user ID from o365, so all good this part)

In powerapps, I could create different views based on different DB, but I am hoping I could do something so that if user is supposed to be using db1, the pass-thru sql view selects * From db1.table

[v-albai-msft]

Hi @smallpotatoe ,

Are db1.table and db2.table two SQL views created in SQL Server?

If yes, when adding SQL connection, you should select these two views together as your datasource. Then for example in a gallery, you can use formula like below to display different view data for different users(Since you are retrieving user ID from O365, you should use "Office365Users.MyProfile().Id" to judge the current login user):

Switch(Office365Users.MyProfile().Id, “user1ID”, db1.table,”user2ID”, db2.table)

For more information about how to create and use SQL Server Views in PowerApps, you can refer to below article:

https://powerapps.microsoft.com/en-us/blog/using-sql-server-views-in-powerapps/

Best Regards,

Allen

[smallpotatoe]

Hello @v-albai-msft 

No, there is only one view in SQL server.

So let's say the view is as: Select * from DB1.Table

I don't mind employee A accessing DB1.Table. But if employee B is logged in, I want him to access DB2.Table. Essentially what I am asking is, is there anyway in PowerApps to switch DB1.Table or DB2 table?

In SQL, I can do that by declaring variable. "Declare @DB1 as DB1, @DB2 as DB2" and then in SQL I can have Select * from (case when employee A then DB1 else DB2).Table

Also I followed your example for testing. If I put 'db1.table', I get unexpected error: