none
Database Admin

    Question

  • What are the permissions for Db Backup Admin? Is he can able to take the backup of System databases also?
    Friday, September 06, 2013 5:58 AM

Answers

  • When speaking about Permission term in SQL SERVER , It has two parts:

    1. Server Permissions
    2. Database Permissions

    And from another point of view, the SQL SERVER ROLE also has two parts:

    1. Server-Level Roles
    2. Database-Level Roles

    In fact the Security Model of SQL SERVER is a hierarchical model. Therefore,

    • If a SQL SERVER Login has sysadmin Sever-Level role, can perform any activity on server like backup any databases.
    • If a SQL SERVER Login does not have sysadmin Sever-Level role, you have to map this SQL Login to the database you want to use. This mapping automatically create a new Database User with the same name of the SQL Login.
    • Now you have two choices:
    • GRANT BACKUP permission to that Database User.
    • Add db_backupoperator Database-Level Role to that Database User.

    For sample script see this link:

    Backup rights

    Another useful links:

    Principals

    Permissions Hierarchy


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    • Edited by Saeid Hasani Friday, September 06, 2013 6:57 AM
    • Proposed as answer by Saeid Hasani Friday, September 06, 2013 6:59 AM
    • Marked as answer by S.Vijay Kumar Friday, September 06, 2013 7:01 AM
    Friday, September 06, 2013 6:51 AM

All replies

  • Yes you can take the backup of system databases also.

    Please refer the below link:

    http://technet.microsoft.com/en-us/library/cc966495.aspx#E0DB0AA


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by vr.babu Friday, September 06, 2013 6:18 AM
    Friday, September 06, 2013 6:07 AM
  • At least you need the following:

    At server level: PUBLIC

    At DB level: DB_BACUPOPERATOR.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 06, 2013 6:08 AM
  • What are the permissions for Db Backup Admin?

    Hello,

    What exactly do you mean, members of the database role "db_backupoperator" or ... ?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 06, 2013 6:11 AM
  • When speaking about Permission term in SQL SERVER , It has two parts:

    1. Server Permissions
    2. Database Permissions

    And from another point of view, the SQL SERVER ROLE also has two parts:

    1. Server-Level Roles
    2. Database-Level Roles

    In fact the Security Model of SQL SERVER is a hierarchical model. Therefore,

    • If a SQL SERVER Login has sysadmin Sever-Level role, can perform any activity on server like backup any databases.
    • If a SQL SERVER Login does not have sysadmin Sever-Level role, you have to map this SQL Login to the database you want to use. This mapping automatically create a new Database User with the same name of the SQL Login.
    • Now you have two choices:
    • GRANT BACKUP permission to that Database User.
    • Add db_backupoperator Database-Level Role to that Database User.

    For sample script see this link:

    Backup rights

    Another useful links:

    Principals

    Permissions Hierarchy


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    • Edited by Saeid Hasani Friday, September 06, 2013 6:57 AM
    • Proposed as answer by Saeid Hasani Friday, September 06, 2013 6:59 AM
    • Marked as answer by S.Vijay Kumar Friday, September 06, 2013 7:01 AM
    Friday, September 06, 2013 6:51 AM