locked
db owner and backup permissions RRS feed

  • Question

  • Hi we run 2014 enterprise.  I'm requesting of our dba that he create a database with recovery simple for my peer and I to start using. 

    I'm asking him to give us db owner on this db so we can create schemas, tables, views, procs, do table inserts, deletes etc etc. 

    what sql permission (if any) would allow my peer and I to do a backup once in a while to the default sql directory for backups?  And for that matter a restore from there. 

    Friday, October 9, 2015 3:06 PM

Answers

All replies

  • Hello,

    Please add the login to the db_backupoperator built-in role.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Friday, October 9, 2015 4:47 PM
  • thx, and then restores by us will be allowed?
    Friday, October 9, 2015 4:55 PM
  • Hello,

    For the restores, you will need the dbcreator server role also.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com




    Friday, October 9, 2015 5:08 PM
  • hate to drag the question out but what if we all agree that the only db we can create presently is this one?  Is there perhaps a way to restrict it with a schema?  I do not recall if schemas can be attached to a db just as they are to tables etc.

    Is this approaching admin rights anyway? 

    • Edited by db042190 Friday, October 9, 2015 5:24 PM more
    Friday, October 9, 2015 5:19 PM
  • Hello,

    Maybe creating a store procedure that performs the restore of that database and then provide the user permission to execute it.

    https://msdn.microsoft.com/en-us/library/ms188354(v=sql.110).aspx


    Try with the “execute as owner” option.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Friday, October 9, 2015 5:29 PM
  • To take a backup of a database, you need to member of any of the database roles db_owner or db_backupoperator.

    To restore the database, you need to be dbo, that is owner of the database. Just being member in db_owner is not sufficient.

    If you want to restore a copy of the database in parallel with the existing, you need membership in the dbcreator server role or have the server-level permission CREATE DATABASE.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 9, 2015 9:31 PM
  • Hi 

         Looking a script to find members under  DB_owner  database role and restrict them to take backup on databases in entire sql server.

    Wednesday, February 1, 2017 9:50 PM