May 26, 2015 at 9:42 am
The company I work for insists on being really liberal with db_owner. For the most part, that's not a problem, but security is an issue. Is there a way to give someone db_owner in a db, but remove their ability to do anything in the db's security node?
May 26, 2015 at 9:58 am
robin.pryor (5/26/2015)
The company I work for insists on being really liberal with db_owner. For the most part, that's not a problem, but security is an issue. Is there a way to give someone db_owner in a db, but remove their ability to do anything in the db's security node?
The crux and possible solution to this problem hinges on WHY the company insists on granting db_owner membership to a broad range of users.
If you are the DBA, then you have ultimate control. If the application will still function normally and users can still perform their daily duties without db_owner privillage, then you could simply ignore tradition by removing everyone from db_owner, and then adding them back on a case by case basis only when someone complains and submits a formal change request that is approved by executive management.
You can also experiment with using DENY to block permissions to db_owner role. I've never dig deep into this, so I don't know how successful this will ultimately be, but the following article appears to go into some detail.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 26, 2015 at 1:11 pm
this demand is usually because they do not understand exactly what permissions are required or cant be bothered to maintain granular security, it's not an excuse
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 26, 2015 at 1:23 pm
robin.pryor (5/26/2015)
The company I work for insists on being really liberal with db_owner. For the most part, that's not a problem, but security is an issue. Is there a way to give someone db_owner in a db, but remove their ability to do anything in the db's security node?
DBO membership allows users to do things like creating and dropping objects.
What you can consider as a stop gap measure is removing users from db_owner role, and then adding them to db_datareader and db_datawriter roles. Also "GRANT EXEC TO [User];" will grant user or group execute permission on all stored procedures. The above is the maximum you want to grant to an application or ad-hoc user for whom you don't know what they'll be doing. If the application requires access to create objects, then it's doing something weird (not that many database applications arn't just weird).
However, ideally you start by adding a new user or group to public role, and then grant access to specific objects on a case by case basis.
Just out of curiosity, is this an Entity Framework application? You didn't mention, but it smells like EF.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 27, 2015 at 6:16 am
Perry Whittle (5/26/2015)
this demand is usually because they do not understand exactly what permissions are required or cant be bothered to maintain granular security, it's not an excuse
Yep. Same from me too, even worse when vendor documentation state the SQL login needs sysadmin rights. mhhh really? Nope, just plain lazy.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply