I'm designing a multi-tenant database where each tenant is given a corresponding database user. The user is assigned access rights to the schema associated with the tenant and specific rights to objects in the dbo schema.
Once I've identified the tenant, I want to switch to the appropriate user context by executing a SQL statement like the following:
EXECUTE AS User = 'Tenant1' WITH NO REVERT
When I execute this command using the ExecuteSqlCommand of the DbContext's Database property, everything seems to work correctly. When I later make changes to the model using Linq and call the method
I get a series of exceptions:
An error occurred while starting a transaction on the provider connection. See the inner exception for details.
with an inner exception of:
A severe error occurred on the current command. The results, if any, should be discarded.
Is it possible to change the user's execution context in this way and, if so, what is the best way to do so?
Have you tried myDbContext.Submit() or SubmitAll ? I cant remember excatly, but it should be something like that...