Good practice to modify MSDB stored procs?

Question

At work, we've working on SQL Server 2008 now, and my boss seems a little bit paranoid about security, so instead of having people be sysadmin (or whatever they need to be to own/modify jobs) he's been editing stored procs in MSDB (such as sp_update_job) and adding a role to the TSQL that checks for required permissions (such as adding SqlAgentOperatorRole). My gut tells me this is not the right thing to be doing, but I can't find anything online stating it's bad practice, so I'm curious what experienced SQL Server folks have to say on the issue.

Answer 1

If your boss is in a management role and not a development one, he shouldn't be editing sps or writing TSQL, but approving your request for additional training on security.

Answer 2

Well it's always a bad practice to change system procs. If you were to upload a new release, the changes could very well be overwritten. I believe you could also have a problem with vendor support if you have changed these things and that is the cause of your problem. It might even invalidate your liscense with some products.

Answer 3

Once upon a time, I modified the SQL 2000 Maintenance Plan system to use SQL LiteSpeed and to support differential backups. This required (I think) modifying a few system procedures and tables in msdb. Doing this made me paranoid, as any subsequent patch or service pack provided by Microsoft would either break my code (me=in big trouble), or my code would break their service pack (me=dead meat). To cope with this, I kept meticulous records of what I did, and maintained a thoroughly tested rollback script so that when (not if) the SPs were released, I could fist roll the servers back to their prior state, patch them, and then re-apply my changes, adjusting my code if and as necessary. I had complete control over the systems affected, and never had any serious problems with the setup.

If you're careful and cautious enough and you plan ahead, you can get away with stunts like this. But... Maintenance Plans were a cute side-light and fairly simple to hack, whereas security is a jar of nitroglycerine sitting in a pool of radioactive waste. It could be done, but I really wouldn't want to mess with it.