1

I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside that schema, and I also do not want them to have create/drop permissions for the schema itself. What is the least permissions to grant this type of access?

Currently, I set up the schema training with dbo as the owner. Then I created a role called training_modify and added the training schema as a securable with "Select", "Update", "Insert", and "Delete" explicitly granted. Finally, I will add users to that role.

Is this the right approach? Does it have any unintended consequences?

Improve this question

1 Answer 1

Reset to default
3

Is this the right approach?

This is the correct approach, users with that role will only be able to perform CRUD operations on objects in the training schema and nothing else. This is the least privilege approach and is correct.

True least privilege would actually be assigning permissions to only the specific objects in that schema, but as you've indicated users should have access to all objects, then applying permissions schema-wide is appropriate.

I do not want them to have any other permissions on any other object outside that schema

You may not actually want this. Consider the public server role. For any object that does not have explicit permissions (GRANT or DENY), permissions are inherited from the public role for a user (any user, all users).

Since you cannot revoke public membership from a user, you would need to explicitly DENY permission on every single object just to prevent users from seeing the object listed in Management Studio - is this really necessary? They have no access beyond seeing the object exists, users still cannot interact with them.

Public access grants users, by default, nothing more than access to a few system catalog views to allow all users to view the name and definition of basic objects and some basic DB/Server info (see this article for more info).

Improve this answer
3
  • Thank you - extremely informative! Agreed that the rights coming from the public role are fine. I just want to avoid adding users to the datareader/datawriter/ddladmin roles, and it sounds like the current approach will avoid that. Thank you again.
    – OverflowingTheGlass
    May 19, 2020 at 12:49
  • does this allow for create table access? or is there something else I need to grant to allow them that for this schema only?
    – OverflowingTheGlass
    May 19, 2020 at 15:27
  • No, CREATE TABLE would require GRANT CREATE TABLE TO <user_or_role>;
    – HandyD
    May 19, 2020 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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