Many SQL developers and admin have asked me a question, about creating a backup operator who has minimum possible permisions and is able to perform job of database backup and restore.To create minimum permission backup restore role user database for SQL Sever 2008. Using below mentioned script minimal permissions are needed for backup and restore operations by the user created by using this script.
Following are permissions /roles are assigned to the user at SQL Server and database level :
At SQL Server instance level following roles are to assigned:
dbcreator public
Using Selected Database following roles are to assigned:
db_backupoperator db_denydatareader public
As a result user / operator created has minimal permissions for database access and can create backups of selected database. For downloading the script visit following link, http://gallery.technet.microsoft.com/Create-Minimum-Rights-4d746f7d