locked
Permission required to perform Backup RRS feed

  • Question

  • hi,

    I have SQL Server 2005.
    Please advice me, what permission is required at Server level (Server roles) + Database level (Database roles) to perform a Database backup.

    Thanks in advance!

    Monday, October 12, 2009 12:28 PM

Answers

  • Server level - PUBLIC ROLE
    Database level - DB_BACKUPOPERATOR .
    This is the least privilege to take backups for a database.

    Thanks, Leks
    • Marked as answer by RubSay Tuesday, October 13, 2009 4:36 AM
    Monday, October 12, 2009 5:25 PM
  • thanks Leks.
    I like your answers as you refer to the least/minimum previlages which is with with security point of view.

    However the below link that i came across refers to a lot more other permissions. Can you please have a look. Thanks.

    Permissions Required for Backup and Restore section @ http://technet.microsoft.com/en-us/library/cc966495.aspx#E0DB0AA
    • Marked as answer by RubSay Sunday, October 18, 2009 11:06 AM
    Tuesday, October 13, 2009 9:07 AM

All replies

  • BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. Login should have either sysadmin permission at server level or db_owner/db_backupoperator permission at the database role level for performing the backup operation.


    You can refer "Permissions section" in the following link for more information about permissions story for backup operations.

    http://msdn.microsoft.com/en-us/library/ms186865.aspx




    Thanks,
    Sateesh Pragallapati.


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, October 12, 2009 1:18 PM
  • The minimum level of permissions required are that you either grant BACKUP DATABASE and BACKUP LOG on each database that you want the user to be able to backup or you add them to the db_backupoperator role in each database you want them to backup.

    Admin authority - database owner or sysadmin should only be granted to an account that you want to be able to perform any operation, without limitation.  (Sysadmin across the entire instance and database owner for an individual database.)
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, October 12, 2009 2:25 PM
  • Server level - PUBLIC ROLE
    Database level - DB_BACKUPOPERATOR .
    This is the least privilege to take backups for a database.

    Thanks, Leks
    • Marked as answer by RubSay Tuesday, October 13, 2009 4:36 AM
    Monday, October 12, 2009 5:25 PM
  • Thanks everyone. how about permissions for performing a RESTORE??
    • Marked as answer by RubSay Sunday, October 18, 2009 10:39 AM
    • Unmarked as answer by Michael Hotek Sunday, October 18, 2009 5:55 PM
    Tuesday, October 13, 2009 4:36 AM
  • You would be needing DBCREATOR role at the server level.
    With this permission -- Members of the dbcreator fixed server role can create databases, and can alter and restore their own databases.
    Thanks, Leks
    Tuesday, October 13, 2009 8:31 AM
  • thanks Leks.
    I like your answers as you refer to the least/minimum previlages which is with with security point of view.

    However the below link that i came across refers to a lot more other permissions. Can you please have a look. Thanks.

    Permissions Required for Backup and Restore section @ http://technet.microsoft.com/en-us/library/cc966495.aspx#E0DB0AA
    • Marked as answer by RubSay Sunday, October 18, 2009 11:06 AM
    Tuesday, October 13, 2009 9:07 AM
  • I just gave you the answers with least necessary permission to perform backup / restore . If you are happy with the link http://technet.microsoft.com/en-us/library/cc966495.aspx#E0DB0AA , i am fine .
    Thanks Rubsay!

    Thanks, Leks
    Tuesday, October 13, 2009 9:10 AM
  • If the database doesn't already exist, you need CREATE DATABASE permission.

    If the database that you are restoring to already exists, then you can also restore if you are the direct owner of the database.  If you are not the direct owner of the database, but only a member of the database owner role, you can't restore the database, because this would not always work in the case of a damaged database where you can't read the members of a role from within the database.

    The exact set of permissions for a BACKUP as well as a RESTORE are very clearly documented in Books Online.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Sunday, October 18, 2009 6:06 PM