Setting Session Context

For anyone who has a similar issue, this is how I was able to resolve it:

1) Create a table-valued function in the database. All this function does is call the sp_set_session_context stored proc in such a way as to completely disable row-level security in the database. We have an admin flag, that, if set to true, disables row-level security, so I just call that. However your system is set up may vary, and I was never able to find a way to pass user info to this function. What this function returns isn't really relevant.

2) Create a view for every table that you will want to use in Power BI. The query for that view will just select * from the table and the table-valued function you created in step 1. Like:

SELECT TAB.* FROM <schema>.<table> TAB, <schema>.DisableDatabaseRowLevelSecurity();

This is also a good time to include any new columns allowing you to add multi-column relationships in Power BI. Our database is multi-tenant, so most table have a composite key made of a tenant id and a normal id. Power BI cannot create relationships on multiple columns, so add a new column to the view that is a concatenation of the FK columns. Like:

SELECT CONCAT(<col 1>, <col 2>) unique_id, TAB.* FROM <schema>.<table> TAB, <schema>.DisableDatabaseRowLevelSecurity();

If every table you will use in Power BI has a view like this, you can easily create relationships between tables that have composite foreign keys.

3) Add all those views to Power BI as queries and set up the relationships appropriately, making sure to include any tables necessary for row-level security to be calculated by Power BI. In our database, one table controls this, so I included all the tables along the way to reach there.

4) Add a new Users role to Power BI, and set up the USERNAME() function with the instructions here. This allows you to pass in any internal user id that you need, so that USERNAME() will give you something pertinent to your system, and not just an email or domain account or something. Then on the table that control your row-level security, add a new rule for the Users role that filters to just their id. Like "[user_id] = USERNAME()" or something.