question

Magnus Ahlkvist avatar image
Magnus Ahlkvist 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?
sql-server-2005backupsecurity
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, that's what I was missing!
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
cool. every day is a learning day :)
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
It worked for me! no need of sa role db_backupoperator is enough!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.