How to define user as dbo of a specific schema

Question

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.

Answer 1

GRANT CONTROL ON SCHEMA::<schemaname> TO <username>; will grant all permissions applicable to schemas and schema contained objects. However, it will not grant any permission applicable to non-schema objects. Case in point it does not apply to full text catalogs, which are not schema bound objects (you do not specify a schema when creating a FT catalog) and which require the special CREATE FULLTEXT CATALOG permission.

In your case you have to review your entire security model and decide what permissions and actions are required/permitted to your tenants. There is no easy one-size-fits-all answer. If you grant to your tenants the liberty of acting like a full db_owner, then you have to grant them a true database. You won't be able to sneak in a schema and pretend is a database. The SQL security model just doesn't work like that, and you better play along rather than try to circumvent it.

Answer 2

I don't think it is possible. If they are dbo, then they can do anything necessary in that database. If they are not dbo, they are limited. Database owner means just that -- the owner of that database.