asked
db_backupoperator
Now I'm going to reveal my poor DBA skills... I have a user in a database which is in db roles **db\_denydatareader**, **db\_denydatawriter** and **db\_backupoperator**. Is it possible to allow this user to schedule backup jobs without adding him to server role sysadmin?
You need to add this user to the sqlagent operator roles in msdb. These are special system roles only in msdb, designed to allow a user to view, alter and administer jobs. There is a distinction between alter/admin of self-created jobs, or all jobs: [SQL Server Agent Fixed Database Roles][1] SQLAgentReaderRole should be sufficient to allow the user to create and administer their own jobs locally. [1]:
http://msdn.microsoft.com/en-us/library/ms188283.aspx
My first reaction would be to ask if (s)he has other permissions in other databases ... the ability to access/update data in a data is removed from running a backup. unless they are denied access to system databases, where the backup activity is logged - that might cause issues. My advice - try it on test. My expectations - you should be fine. My prediction - I could be wrong.
I have tried it in test, by logging in as that login. Adding to SQLAgent* roles in MSDB as WilliamD suggested was the missing link.
It worked for me! no need of sa role db_backupoperator is enough!
