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
- No create/drop/alter permissions for the schema itself
- The role and individual users should not require membership in
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:
dboas the owner
dboas 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.
dbostill own the role? or should
TestOwnerown both the role and the schema?
- Is it accurate that if I took away the
CREATE FUNCTIONpermissions, 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
EXECUTEpermission on the schema). If this is accurate - are there any pros/cons between this approach and changing the owner of the schema?