September 4, 2019 at 7:08 pm
I'm trying to grant a database role the ability to execute both DDL and DML statements at the database level so that it covers all schemas and all objects in the database. What I'm discovering is that I still have to grant at the schema level which can be tedious since some databases have 10+ schemas and will have additional created in the future or I have to grant alter at the database level which is more than I want to allow. Is there another step I can include in here to allow what I'm trying to accomplish?
CREATE ROLE TestRole
grant create table, delete, execute, select, update, insert to TestRole
I noticed the DML statements are fine but I have to do this to allow for the DDL
grant alter to TestRole
September 4, 2019 at 10:11 pm
That would be expected behavior when not owning the schema. There are several posts up here with the same type of issue, it's one of those that stumps people fairly often. Here is a link to a similar post up here and towards the end is an example of a DDL trigger to use to restrict other actions available with alter permissions:
Sue
September 5, 2019 at 11:28 am
I suppose I'm trying to get too granular which is leading to more work than necessary. My hope was that grant alter could be broken down into more specific permissions rather than giving the ability to all things it allows.
You mentioned schema ownership which leads me to another question. Suppose I have the three schemas: Schema1, Schema2, and Schema3. If all three are owned by dbo and a database role, Role1, has alter permission on the dbo schema, then will that also give them alter on Schema1-3? What if Schema3 is owned by database role, Role3? Would Role1 then only have alter on Schema1 and 2 since Schema3 is now owned by Role3? Conversely, would Role3 only have alter on Schema3?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply