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:


Permissions Hierarchy

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

Database Admin


Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s