1

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

3
  • 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? Commented Dec 19, 2013 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! Commented Dec 19, 2013 at 17:54
  • Well, stop doing that, and only grant them read access to the schema(s) you want them to access. Commented Dec 19, 2013 at 17:57

1 Answer 1

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]

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