December 2, 2016 at 12:48 pm
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?
December 2, 2016 at 1:44 pm
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
December 5, 2016 at 6:08 am
Sue - I must not be setting it up correctly when trying to follow your advice. What permissions do I grant at the database level that won't apply to all schemas? For example, I grant alter permission to the role at the database level and they can also create tables in the dbo schema which I do not want.
December 5, 2016 at 7:11 am
RonMexico (12/5/2016)
Sue - I must not be setting it up correctly when trying to follow your advice. What permissions do I grant at the database level that won't apply to all schemas? For example, I grant alter permission to the role at the database level and they can also create tables in the dbo schema which I do not want.
Have you thought about using Deny? Deny overules Grant, so if a user is GRANTED permissions to create tables on all schemas, but is DENIED permission to create tables on the schema dbo, they can't. Provided, of course, they aren't a sysadmin, dbowner, etc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 5, 2016 at 8:34 am
I was able to get what I need by using a combination of making the role the owner of all the schemas they need access to and by specifying deny on the dbo schema. Thanks for the help!
December 5, 2016 at 1:12 pm
If I recall (I don't have a test setup for this), you just need to grant CREATE TABLE (PROCEDURE, VIEW, etc.) to the login, then grant CONTROL on the schemas that you want to allow them to create these objects in. There may be a lower permission that you can grant on the schemas, but this should cover it. Some testing will need to be done, of course.
December 6, 2016 at 9:38 am
RonMexico (12/5/2016)
Sue - I must not be setting it up correctly when trying to follow your advice. What permissions do I grant at the database level that won't apply to all schemas? For example, I grant alter permission to the role at the database level and they can also create tables in the dbo schema which I do not want.
You don't grant at the database level. The role is granted permissions at each schema level
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply