When speaking about Permission term in SQL SERVER , It has two parts:
And from another point of view, the SQL SERVER ROLE also has two parts:
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:
Another useful links:
I wrote this post based on one MSDN Forum question. For more discussion see this motivated link :