SQL Server Performance

Allow Someone to Restore DB with Granting DBO?

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, Nov 1, 2006.

  1. DBADave New Member

    We have a consultant who would like to restore a test database on an as-needed basis, however I am hesitant to grant dbo permission to his id. Is it possible to give someone the ability to restore a SQL 2005 database without that person having dbo permission? I'm guessing the answer is no.

    Dave
  2. madhuottapalam New Member

    Hi,

    Yes, it is possible. Members of the dbcreator fixed server role can create, alter, drop, and restore any database


    Madhu
  3. DBADave New Member

    I guess that's a little more restrictive then dbo.

    Thanks, Dave
  4. satya Moderator

    Try with db_backupoperator also if you have issues with DBCREATOR server role.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. DBADave New Member

    I tried db_backupoperator and unless I was doing something wrong, it only allowed the id to perform database backups, not restores.

    I also thought about taking advantage of the "run as" feature in a SQL Job.

    Thanks, Dave
  6. satya Moderator

    Hm, I will try on test one and come back to you. I'm sure this was good when I tried earlier.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  7. DBADave New Member

    I may be doing something wrong. Even when I grant the login id DBO permission I receive a message indicating the user does not have permission to restore the database.

    How is that possible?

    The login id is DBA
    DBA is a user of the database, Test1
    DBA is a member of db_owner in the Test1
    Default schema is dbo
    Default database is Test1
    DBA is a user of msdb
    DBA is a member of SQLAgentUserRole

    The error occurs if I run a scheduled job owner by DBA to restore the database or if I try to restore the database by right-clicking and choosing restore.

    Any idea what I'm missing? Am I suppose to be doing something with "Effective Permissions" when granting DBO access to a database?

    Dave
  8. DBADave New Member

    Satya,

    It works if I grant dbcreator permission. Why is dbcreator permission required if the id is a member of db_owner and the Restore Database statement is NOT using WITH REPLACE.

    Dave
  9. madhuottapalam New Member

    Hi,

    To create a database on a server you have to have Server Level Permission. Database level permission is not enough. DBCreator is a server level role where as db_backupoperator is a Database level role. SO you can not restore a database when the role is db_backupoperator. DBO is database owner to the particular database. To create a new database you should be member of either DBCreator or SysAdmin.

    Refer Server Level Role and Database Level Role in BOL

    regards

    Madhu
  10. satya Moderator

    Dave
    As explained above with DBCREATOR it shouldn't be a problem.
    I'm still trying to recreate the scenario on my test bed, the result is same as yours...failing to restore. I will come back to you once i have my options.

    BTW, if you're using Windows authentication and with DBCREATOR role there shouldn't be much problem for this consultant to access the server.

    quote:Originally posted by DBADave

    Satya,

    It works if I grant dbcreator permission. Why is dbcreator permission required if the id is a member of db_owner and the Restore Database statement is NOT using WITH REPLACE.

    Dave

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  11. DBADave New Member

    Madhu,

    I'm not using the "WITH REPLACE" option so according to BOL it should work. In BOL under "New Information - SQL Server 2000 SP3." is a chart indicating that members of db_owner can execute the "Restore" command. BOL also says "If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database." Perhaps only the actual owner (dbo) can restore a database and not members of db_owner, but that doesn't make much sense to me if it is true.

    Dave
  12. satya Moderator

    Is this a SQL 2000 or 2005 related questions?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  13. DBADave New Member

    2005. This isn't an issue in 2000.
  14. DBADave New Member

    Someone pointed me in the right direction. I searched BOL again and found the statement below.

    RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

    Thanks, Dave

Share This Page