So I created the following users which owns single schema:
userA -> owns -> schemaA
userB -> owns -> schemaB
userC -> owns -> schemaC
The userA should be able to access dbo & schemaA schemas, but should not be able to access (i.e select/update/insert/delete...) schemaB data!
May be I can do something as below:
DENY SELECT ON schema::[schemaB] TO [userA]
DENY DELETE ON schema::[schemaB] TO [userA]
DENY UPDATE ON schema::[schemaB] TO [userA]
DENY INSERT ON schema::[schemaB] TO [userA]
GRANT SELECT ON schema::[dbo] TO [userA]
GRANT DELETE ON schema::[dbo] TO [userA]
GRANT UPDATE ON schema::[dbo] TO [userA]
GRANT INSERT ON schema::[dbo] TO [userA]
However, is there any better approach for doing this, so the userA can only access dbo and schemaA, and not schemaB & schemaC? And same vice-versa for userB & userC!
REVOKE
ing all permission for everyone (rather thanDENY
ing), then onlyGRANT
ing to the users or roles that need access. This gives us a pretty flexible approach to db access security.