I'm creating a multi-tenant application that will utilize a single database with multiple schemas. Each tenant will have their own respective login/password. I was able to restrict view permissions to a specific schema.
The only issue I keep coming across is when updating schemas. Currently I have my C# code login using the proper schema credentials and then run SQL code whenever I want to update a specific (or all) schemas.
At first, I tried to alter the table, and then realize I didn't have enough permissions. So I tweaked the permissions again. Now, I am trying to add full text indexing on all the schemas and it's telling me that I don't have permissions.
My question is simple, how can I define a user's permissions as
the database owner for a specific schema so that I won't have any issues with any script that is run on the database.