Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.