How to grant permissions to allow anything inside a SQL Server schema?

Question

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?

Answer 1

Ownership chaining is generally not something to worry about. It only implies permissions for DML (INSERT, UPDATE, and DELETE), SELECT, and EXECUTE operations. It does not allow for CREATE, ALTER, DROP, etc.

The tricky part / nuance here is that, by default, objects have a NULL owner, meaning that their ownership is implied as being the owner of the schema in which they exist. So, in this particular case, with:

1. the training schema being owned by dbo, and...
2. the training_modify user being able to create stored procedures and/or functions in the training schema,

the training_modify user, who can't do DML against a dbo. table, can simply create training.[proc] which does the DML and then execute that stored proc and it will allow for doing DML against the dbo. table.

Changing the owner of the training schema fixes this issue (assuming that you want no access to dbo. objects). You can create a database-only user (i.e. WITHOUT LOGIN) and make that user the owner of the training schema. This user will not be used for anything else; it only exists to allow the owning principal_id of the training schema to be different from that of the dbo schema, thus breaking ownership chaining.

Please note:

• The documentation for CREATE ROLE states:

  The owner of the role, or any member of an owning role can add or remove members of the role.

  Meaning: the owner of a role does not affect ownership chaining. HOWEVER, anyone in the training_modify role probably shouldn't be able to add/remove others, so best to keep dbo as the owner of the role (not the schema).
   

• The documentation for ALTER AUTHORIZATION, under "Special Cases and Conditions" states:

  When ownership is transferred, permissions on schema-contained objects that do not have explicit owners will be dropped.

  Meaning: you will need to GRANT permissions on the schema after executing ALTER AUTHORIZATION on it.
   

• As far as pros/cons between, that depends greatly on what objects will exist in the dbo schema, how they will be accessed, and what the training is supposed to accomplish and how flexible this setup needs to be. I'm not privy to any of that info, so for now I will simply generalize by saying:

  • schema owner is dbo and not allowing CREATE PROCEDURE:
    1. CON: training person cannot play with creating procs and functions
    2. ???: if training folk need access to dbo. objects, then modules can be created in training schema (CON if lots of objects and/or different actions required, else just "meh").
  • changing schema owner and allowing CREATE PROCEDURE:
    1. PRO: training person can play with creating procs and functions
    2. ???: if training folk need access to dbo. objects, either explicit permissions will need to be granted to the training_modify role, allowing ad hoc access to those objects (CON), or modules need to be added to dbo schema to do that access and EXECUTE / SELECT permission can be granted on those (CON if lots of objects and/or different actions required, else just "meh").
        

  Assuming there's not too much in the dbo. schema and/or the required access is relatively simple, I would opt for changing the schema owner and allowing for CREATE PROCEDURE, etc.

Answer 2

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.