Thread: DBO Privileges
05-29-04, 18:16 #1Registered User
- Join Date
- May 2004
Unanswered: DBO Privileges
I have just noticed something very discomforting.
I was told that a user with DBO privileges is able to alter their own database. A conversation of course began to where I was in disagreement with him. The ultimate test of course would be setup the scenario. To my surpise he was right!
I checked the BOL documentation and my concerns were verified.
I have checked permissions on the user I created as well as on a user that previously exists on the MSSQL Server. Only DBO permissions were given to the tested users.
I thought maybe this had something to do with the autogrow setting which is a setting we would enable on a dedicated MSSQL Server but not on a shared MSSQL Server. I toggled this option and the DBO was still able to make size changes to their database.
This is very upsetting as we charge for additional reserved database space. Aside from that, we wouldn't want to have a user with unlimited resources to the server. I could easily fill up a hard drive if I were to update the autogrow setting of the database as DBO and run an infinite loop that would insert data into tables.
I then tested the ability for a user to restore a backup and to my surprise it worked without error for the DBO only privileged user. The DBO user was also able to restore previously dated databases assuming that they knew the file name which would not be hard to guess since it is appended with a date stamp (My_Database_20042905.BAK).
Why is this? Is there a way to correct this and prevent the DBO user to only have access to their database but not the above mentioned type privileges?
Last edited by A3VDO; 05-29-04 at 18:25.
05-29-04, 21:03 #2Registered User
- Join Date
- Apr 2004
- Kansas City, MO
DBO stands for Database Owner so of course they have these privileges. If you want to restrict their privileges, you need to look at database roles and setup/grant permissions to users accordingly.MeanOldDBA
When life gives you a lemon, fire the DBA.
05-30-04, 18:11 #312 Monkey Method
- Join Date
- Feb 2004
- San Antonio, TX
1. figure out what privieldges you want the users to have
b. create a role and grant those privs to the role
III. make the users members of that role.
making someone dbo is the equivelent of making someone an administrator
sql does provide very good server and database roles for your use.