September 18, 2012 at 7:46 pm
Hi,
I manage an environment where all developers are given db_owner access to their databases. I have a schema called app in these databases which is linked to the role db_app.
I'd like to do this:
1. Deny any members of db_owner access to the objects with this schema (deny all permissions)
2. Only allow users who have access to the db_app role to have full access to this schema's objects
Note that the developers would be in the db_owner role as well as the db_app role (only the developers who need access). Here's what i've tried to do:
1. Create the schema and role, and give the db_app role full permission to the schema
2. Give the specific developers access to the db_app role
This does not seem to work as even if the developers do not have access to the db_app role they still have permissions on tne schema's objects. I can deny the developers permission explicitly, based on their login. However this then overrides the fact that the db_app role has full access.
Looking for some advice please, any help is appreciated.
Thanks,
Mike
September 18, 2012 at 8:05 pm
mpartridge (9/18/2012)
Hi,I manage an environment where all developers are given db_owner access to their databases. I have a schema called app in these databases which is linked to the role db_app.
I'd like to do this:
1. Deny any members of db_owner access to the objects with this schema (deny all permissions)
2. Only allow users who have access to the db_app role to have full access to this schema's objects
Note that the developers would be in the db_owner role as well as the db_app role (only the developers who need access). Here's what i've tried to do:
1. Create the schema and role, and give the db_app role full permission to the schema
2. Give the specific developers access to the db_app role
This does not seem to work as even if the developers do not have access to the db_app role they still have permissions on the schema's objects. I can deny the developers permission explicitly, based on their login. However this then overrides the fact that the db_app role has full access.
Looking for some advice please, any help is appreciated.
Thanks,
Mike
You have 2 problems from what I'm reading.
1st Deny trumps any grant. If a user is denied permission through any means, role, directly, etc then they do not have that permission regardless of what grants they do have.
Which brings us to number 2. The exceptions. db_owner and sysadmin are major exceptions. Members of either of these roles (database/server respectively) can not be denied any permissions that the role grants them. ie a member of db_owner can't be denied anything within that database and a member of sysadmin can't be denied anything on the server. As I understand it this is because if a user is a member of one of those roles permissions aren't actually checked.
Your best bet is probably to move the schema and everything under it to another database with seperate permissions. Don't grant db_owner to anyone, just grant the permissions to the schema itself. That way the user has access to anything under the schema but access to specific objects can be denied at need.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply