Sql-server – How to give user access permission to dbo and single custom schema

sql serversql-server-2008

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!

Best Answer

When you create your users userA, userB, userC, they have no permissions at all (unless you grant some permission to public database role, but by default no permission is granted to public)

So they cannot access nothing, even dbo schema, so you have no need to revoke/deny any permission.

Then you create schemaA, schemaB and schemaC (there is no need to make any particular user the owner of these schemas) and grant the permissions to your users as follows:

GRANT CONTROL ON schema::[dbo] TO [userA];
GRANT CONTROL ON schema::[schemaA] TO [userA];

GRANT CONTROL ON schema::[dbo] TO [userB];
GRANT CONTROL ON schema::[schemaB] TO [userB];

GRANT CONTROL ON schema::[dbo] TO [userC];
GRANT CONTROL ON schema::[schemaC] TO [userC];

Of course you can do the same thing granting the permissions to Roles, not only to individual users

Related Question