db_dataowner priveleges

pma111 used Ask the Experts™
Can I ask for some specific scenarios when a user would require db_dataowner privileges? I am looking at security permissions and have quite a number of users with db_dataowner over a few databases, but before asking for that to be removed, it would help knowing any particular reasons why that level of access may be required. For info none of the users are DBA's! But some do have systems admin responsibility for the over-arching applications which use these databases for records storage.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

users with the db_owner role can basically do anything with the database, alter users roles and even remove it. The sysadmin role sits on top of that and doesn't need to have db_owner privileges. Regular users should not be a member of this role. Just give db_read and db_write so those users are able to alter data.

ina_donInformation Systems Expert


I think you need to look at the applications that they use and what functionality they have in the DB. This could have been someone who didn't want to go into the trouble of securing the application properly if users are authenticated directly to the DB. If each user that is granted to the application has an account on the DB, then to make sure they can work properly, you'd need to setup correct permissions for them to read/write data, execute stored procedures and access views and functions.
Certified Database Administrator
Once something is there it may be challenging to identify why it's there.
Some questions to ask or find answers for.
Is this a production environment, this will limit what you can do and not do right away.
What kind of users are they, windows groups and members of those groups, service accounts, windows user account, sql logins.

I work in large datacenters so there are many times where we are going to decommision a server and we run a job which checks all current connection to sql server and store the user name, host name of the connection.

Generally if it's a production environment the only people that have we give db_owner is database administrators.
If another user requires db_owner they have to jump thru a lot of hoops to get it.

And yes you have those inherited systems where there is a application which runs stored procedure and  they go and give db_owner to the login used by the application.

And while you are there, if you have a lot of db_owners don't be surprised to find a few sysadmins sprinkled in.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial