SQL Server Grant CRUD to Users on One Schema 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?
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).