I am interested at implementing a multitenancy solution using hibernate as provider and SQLServer as database. I am using the schema based approach meaning one database and different schemas.

Specifically, my question is how to alter the execution schema with SQLServer. I have used this approach with MySQL , PostgreSQL and Oracle and i was able to successfully switch from one schema to an other, noting that the schema concept differs from one database to another.

However, with SQLServer, I understand that there's no such thing as altering execution schema. I am aware of the possibility of switching the default schema of a given user in case the user is not granted the sysadmin role. I am also aware of the impersonation concept using the folllwing query : . Impersonation allows one user to impersonate an other user, which gives the possibility to change the current user.

Switching the current user allows by definition to switch the execution schema since once the user is changed the execution schema is the new user's default schema. However, the impersonation has a limitation since we can't perform impersonation over 32 times.Although it's possible to execute revert after each impersonation in order to avoid reaching the limit impersonation number, this solution is not suitable in my case and I'm looking for an alternative.

Does anyone have any suggestions about multitenancy implementation using SQLServer and a schema based approach?

Also is there any solutions other than the ones I mentioned to switch execution schema wih SQLServer.

Your help is very much appreciated.Thank you so much.

What if you have multiple DataSources and the tenantId is used to pick one or the other. This is similar to these examples I added the Hibernate documentation folder.

So, instead of choosing to switch the schema, the Connection is picked from the right DataSource.

Thank you so much for the time you have taken in order to reply to me.
However, this solution does not solve my problem since the situation is the following : one datasource, multiple schemas.
As I mentionned, I'm implementing the Multitenancy schema based approach.
I would be extremely appreciative if you have any suggestions about specifically switching the schema and sticking with the same approach.
Thank you.

Best Regards

Unfortunately, I'm not an expert on SQL Server, so if changing the current schema is not allowed, the only way to make it work is if you define N DataSources for all the N tenants.

I agree. Thank you so much.