Results 1 to 3 of 3

Thread: DBO Privileges

  1. #1
    Join Date
    May 2004
    Posts
    18

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    simple solution
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •