SQL SERVER BACKUP Database Permission

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 thisSQL Login to the database you want to use. This mapping automatically create a newDatabase 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

I wrote this post based on one MSDN Forum question. For more discussion see this motivated link :

Database Admin

Advertisements