1

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!

|improve this question
  • Create a role with the appropriate permissions and grant the role to the people. – Dan Bracuk Jul 28 '17 at 16:17
  • You mean create a role per user and what about dbo schema? How can I do that? – seUser Jul 28 '17 at 16:20
  • We start by REVOKEing all permission for everyone (rather than DENYing), then only GRANTing to the users or roles that need access. This gives us a pretty flexible approach to db access security. – Shawn Jul 28 '17 at 16:39
  • 1
    You create the Roles and then add the Users to those Roles. Schema can make permission management easy, but Schema permissions apply to anyone who can access that Schema. What you've got above will work and can be another layer of security in your system, but won't be easily changeable if you need something more granular in the future. – Shawn Jul 28 '17 at 16:50
2

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

|improve this answer
  • How can you verify the userA, userB, userC, have no permissions at all? – seUser Aug 1 '17 at 19:19
  • @seUser This is the default behaviour. Any newly created user has no permission unless you grant it to him doing explicit GRANT, adding him to a ROLE, or granting public database role some permission. To verify what permissions your userA has right now you should impersonate him like this: exec as user = 'userA' and check permissions: select * from sys.fn_my_permissions(null, 'database') -- this shows you permissions inherited from fixed roles, then do the same for your schemas: select * from sys.fn_my_permissions('dbo', 'schema'); select * from sys.fn_my_permissions('schemaB', 'schema') etc – sepupic Aug 1 '17 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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