October 2, 2017 at 10:29 am
I'm trying to do this right, but for the love of all that is good I swear Microsoft want me to have a nervous breakdown. SURELY it should be a relatively common requirement to create a Maintenance Plan which backs up all databases on a server and runs using a Proxy? I've never done specifically this before, and it's making me mad (can you tell? 😉 ).
Simply, I need to create a Maintenance Plan which contains several schedules for backing up the databases (Logs, Differentials, etc.). I've created the Proxy (because it has permission to the file share where the backups are being saved) and specified that the Maintenance Plan Jobs should "Run As" that user, but after hours of trial and error (because each attempt to run the backups takes a long time), it's still not working, and I can't help but think there must be a better way of doing this than me going into every database, creating the User I'm using as a Proxy, adding it to the db_operators role, adding it to the db_ssisoperator role in MSDB, granting it execute permissions to various Stored Procedures in the MSDB and Master databases... and so on. It's a tangled mess and, as I say, I can't believe this is the intended way of doing this. I've given up after the umpteenth attempt with an error about the Job not having permission to execute sp_create_subdir in the Master database. I could, of course, grant that permission to the appropriate account, but this is getting ridiculous.
Is there not a way that I can create the Proxy and then just grant it permission to backup every database (e.g. a server role, rather than having to do it for each database) and grant a corresponding Login permission to execute Maintenance Plan Jobs without having to give explicit permissions to all sorts of Stored Procedures in System Databases and memberships of multiple roles in System and User databases?
It's times like this when you can understand why people just grant excessive permissions like SYSADMIN to users. This would have taken me 5 minutes if I'd done that. Instead, I'm trying to do it properly but can find no clear documentation and it's prohibitively complicated.
October 3, 2017 at 7:26 am
Can I ask why you want to use maintenance plans? They're a total pain. I'd use the Ola Hallengren solution.
October 3, 2017 at 8:53 am
I can't argue about how annoying they are, but they do (when they work) give a nice, transparent view on what's being done and on what schedules. With the solution you've mentioned, from what I can tell, I'd have to configure various instances of the script to run (e.g. Full backups, Differential backups) and then schedule each one. I wouldn't have a single place where I could easily view the schedules and what was going to be run for each one.
October 4, 2017 at 1:45 am
You just set up the solution once, and use the Job Activity Monitor if you want to view the schedules. The general advice (here and elsewhere) is almost always to use the Ola scripts. I'd say there's little point reinventing the wheel, but obviously that's up to you. (The SQL Agent jobs run in the context of the SQL Agent service account, so you wouldn't have the proxy issue.)
https://www.brentozar.com/archive/2017/06/ola-hallengrens-maintenance-scripts-now-github/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply