Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a multi tenant environment, where the multiple tenants are in the same database and a schema is used to separate each one of them, how can I create a database user (MS SQL SERVER) for each schema, where the user has only access to his schema?

I know that I can do something like the code bellow, but I would like that by default a user has no access to any other tenant.

DENY SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
GRANT SELECT ON schema::[safe_schema] TO [user_name]

Thanks

share|improve this question

closed as off-topic by Mark Storey-Smith, Max Vernon, Shawn Melton, Martin Smith, Michael - sqlbot Dec 21 '13 at 7:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, Max Vernon, Shawn Melton, Martin Smith, Michael - sqlbot
If this question can be reworded to fit the rules in the help center, please edit the question.

4  
As long as you don't add them to any database-level roles, they shouldn't have access to any other schemas. Are you seeing cases where new users are able to access other schemas? –  Aaron Bertrand Dec 19 '13 at 13:30
    
You are right... my fault is that I'm adding the db_datareader role to the user... and this gives him the read access to every schema. Thanks! –  Guilherme Ferreira Dec 19 '13 at 17:54
    
Well, stop doing that, and only grant them read access to the schema(s) you want them to access. –  Aaron Bertrand Dec 19 '13 at 17:57

1 Answer 1

The solution is to add the Grant permissions and do not add any read or write role to the user.

GRANT SELECT ON SCHEMA::[SCHEMA] TO [USER]

GRANT INSERT ON SCHEMA::[SCHEMA] TO [USER]

GRANT UPDATE ON SCHEMA::[SCHEMA] TO [USER]

GRANT DELETE ON SCHEMA::[SCHEMA] TO [USER]
share|improve this answer

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