September 27, 2017 at 8:20 am
My developer is able to create objects under the dbo schema but not under the other schemas within the database after I gave her db_owner role. For example, there are tables under schemas named REPORT, REF, and INSIGHT. I looked at various forums, posts and documentation, but still not clear why this is so. I thought db_owner could do it all.
Microsoft says "Members of the db_owner role can perform all configuration and maintenance activities on the database".
September 27, 2017 at 8:38 am
trhorner - Wednesday, September 27, 2017 8:20 AMMy developer is able to create objects under the dbo schema but not under the other schemas within the database after I gave her db_owner role. For example, there are tables under schemas named REPORT, REF, and INSIGHT. I looked at various forums, posts and documentation, but still not clear why this is so. I thought db_owner could do it all.
Microsoft says "Members of the db_owner role can perform all configuration and maintenance activities on the database".
Actually, you're running into the design of schemas here. That's intentional. Other schemas aren't necessarily "owned" by dbo, so therefore having dbo privileges doesn't provide control over those other schemas by default. That level of privilege has to be GRANTed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 27, 2017 at 8:38 am
Does your developer by any chance have other permissions that would explicitly deny privileges in those schemas?
You can check pretty easily, make a new user and give them db_owner, they should be able to do pretty much everything, now add them to deny_datareader as well and they can't run any more selects on anything. SQL Server goes with the most denied permission in the case where there conflicts for an object.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply