RonMexico (12/2/2016)
I have a feeling this will end up being an easy answer but I'm struggling with it at the moment. We have a database in dev that has a bunch of schemas in it. I need to be able to give a role permission to have free rein in all schemas except dbo. Meaning they'll be able to create, update, alter, drop, etc in any existing schema (and future schema) other than dbo but will still need to be allowed to select from existing tables in dbo. What's the best way to go about this?
One way would be to create the role then have that role own the schemas where they have free rein. For the dbo schema, you could grant the role select on the schema if you want the members to select from all tables in that schema.
Sue