How to grant permissions to allow anything inside a SQL Server schema? I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:


Select, update, insert, and delete permissions on any object in that schema
Create any object in that schema
Drop any object in that schema
No permissions on any other object outside that schema (except for those granted through the public role
No create/drop/alter permissions for the schema itself
The role and individual users should not require membership in db_ddladmin, db_datareader, db_datawriter, or any other default security roles


In simple terms, I want the users in the role to be able to do anything they want within the schema without affecting/seeing anything outside the schema or the schema itself. What is the least privilege to grant this type of access?

My approach so far:


Create training schema with dbo as the owner
Create training_modify role with dbo as the owner
Grant all permissions on the schema to the role
Grant create objects to the role
Add a user to the role


Here is the code for the above steps:

CREATE SCHEMA training AUTHORIZATION dbo;

CREATE ROLE training_modify AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::training TO training_modify;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO training_modify;

EXEC sp_addrolemember 'training_modify', 'example_user';


Is this the right approach? Does it have any unintended consequences? I am most worried about how ownership chaining may affect this approach negatively with dbo being the owner of the schema and the role (as well as other schemas/objects in the DB), and the alter permission being granted on the schema. 

Also:


Should dbo still own the role? or should TestOwner own both the role and the schema?
Is it accurate that if I took away the CREATE PROCEDURE and CREATE FUNCTION permissions, this would prevent any DML on objects outside the specific schema? I don't think I care if they can create procedures, although I would like them to be able to execute procedures/functions (which I think would be covered by the EXECUTE permission on the schema). If this is accurate - are there any pros/cons between this approach and changing the owner of the schema?
Is this the right approach? Does it have any unintended consequences?
  I am most worried about how ownership chaining may affect this
  approach negatively with dbo being the owner of the schema and the
  role (as well as other schemas/objects in the DB), and the alter
  permission being granted on the schema.


The ALTER permission in the training schema will only grant ALTER permissions to objects in that schema, as stated by Solomon Rutzky in the comments, ownership chaining doesn't apply for DDL, only DML/Execute.

Ownership chaining provides a mechanism for providing limited to access to objects that a user does not have explicit permissions on by chaining the ownership of one object to another. For example, if you have a stored procedure in the training schema that queries a table in the dbo schema and both the procedure and table are owned by dbo then users who have permission to execute the procedure will be able to get the query results from the table they otherwise do not have access to.

This doesn't provide any direct access to the table, the only access to the table is via the stored procedure. This is a method for granting controlled access to an object without needing to expose the underlying object. See this link for more info.

In your specific scenario, to allow users to perform DML/Execute on objects in another schema, you would need a stored procedure/function/view that is owned by dbo and executes the DML/Execute statements against other schemas also owned by dbo. Without this, your users will be prevented from running the commands against schemas/objects outside of training.