December 2, 2010 at 10:18 am
Challenge
We do not want every user who manages Agent jobs to have the fixed SQL Server agent role, sysadmin. Giving every user access to SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole still forbids users from editing jobs created by other operators. None of these SQL Server Agent Fixed Database Roles have permission to change job ownership, either [2].
This is extremely annoying. I could create wrappers around all the job-related stored procedures, such as sp_update_job, but this would not solve the problem that SQL Server Management Studio (SSMS) is hardwired to assume only four roles can affect SQL Agent: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole, and sysadmin. Since we have a lot of people who use this awful piece of software (SSMS), I can't simply create digitally signed wrapper procedures with EXECUTE AS OWNER.
I've tried a number of things to better understand this and see if there is something undocumented I could take advantage of, such as setting the job owner to guest, and none of these tricks worked. e.g.:
EXEC MSDB.dbo.sp_update_job @job_name = 'Test', @owner_login_name = 'sa'
-- insert ssms test step here
EXEC MSDB.dbo.sp_update_job @job_name = 'Test', @owner_login_name = 'guest'
I found a discussion with SQL Server MVP Andrew Kelly where he could not find a solution to the problem, either [3].
Is the SQL Server community satisfied with the way SQL Server Agent works, and if so, why?
Relevant MSDN Articles
[1] Implementing SQL Server Agent Security
[2] SQL Server Agent Fixed Database Roles
Relevant Internet Web Pages
December 2, 2010 at 10:57 am
johnzabroski (12/2/2010)
We do not want every user who manages Agent jobs to have the fixed SQL Server agent role, sysadmin. Giving every user access to SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole still forbids users from editing jobs created by other operators.I found a discussion with SQL Server MVP Andrew Kelly where he could not find a solution to the problem, either [3].
This is inaccurate. I've left the link in question in the quote. Please note his comment:
You can't have it secure yet open at the same time. If you want more than 1 user to edit the same job then you should create a Windows group that both windows users belong to and have that own the jobs. This again assumes both users will have the same permissions or they should not be in the same group.
This works effectively. As a DBA, I'm not ecstatic about the idea that anyone can setup/edit jobs willy nilly on any server. This security lockdown and very specific editability makes sure that you can audit who can do what if you release the ability to create jobs. It's mostly used for dev environments so that developers can create their own jobs for DBA review and processing through the QA/Prod environments, where THEY have the SA role.
Developers don't need god access. They just need to do their jobs. Before I get accused here of being overly critical, I am currently in a developer role and completely locked out, specifically using these rules and settings in Dev with only data_reader rights in QA for bug fixes. I agree with my DBAs on this, except in a few specific instances.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2010 at 11:09 am
Craig,
The next comment (by RLF) in that thread clearly states a Windows Group cannot own a job, which matches the documentation in [1] and [2] referenced above. Then Andrew clearly states in reply that RLF was right, and the original poster clearly states that they have the same problem:
The specified'@owner_login_name' is invalid (valid values are returned by
sp_helplogins [excluding Windows NT groups]).
Then Andrew, in the last reply in that thread, mentions he was wrong.
Lastly, I don't take as God anything Microsoft implements 🙂 Remember, SQL Server Standard Edition, Column GRANT overrides Table DENY, and only in the higher priced editions can you enable 'common compliance criteria enabled' flag. From my perspective, SSMS was designed by people who assumed the users would always be 'sa' or equivalent, and SQL Agent suffers similar design flaws since it does not allow fine-grained execution rights.
As a confession, I am also a developer, but we don't have a "DBA" where I work and I'm the one who fixes things with the database, whether it is jobs or performance-related. Since it isn't a full-time responsibility, I am nowhere near as skilled as some on here, but would like to think my ability to think logically is as good as everyone else's. Also, as a developer, I tend to prefer locking down permissions to execute code using object-oriented security and manage security through the object graph. You can't do this with SSMS, because it wasn't designed with fine-grained security in mind, and so it doesn't correctly address "interaction control" (making security user-friendly). Security isn’t about making all operations difficult; it’s about restricting access to operations with undesirable effects.
December 2, 2010 at 11:25 am
johnzabroski (12/2/2010)
Craig,The next comment (by RLF) in that thread clearly states a Windows Group cannot own a job, which matches the documentation in [1] and [2] referenced above. Then Andrew clearly states in reply that RLF was right, and the original poster clearly states that they have the same problem:
The specified'@owner_login_name' is invalid (valid values are returned by
sp_helplogins [excluding Windows NT groups]).
Then Andrew, in the last reply in that thread, mentions he was wrong.
:blush: And I feel like an idiot for not taking the 3 minutes to keep reading the thread. I was under the same understanding as he was, since some of our DBAs here were intending to do the same thing eventually, and I'd assumed they'd done the research.
Thank you for the correction.
Lastly, I don't take as God anything Microsoft implements 🙂 Remember, SQL Server Standard Edition, Column GRANT overrides Table DENY, and only in the higher priced editions can you enable 'common compliance criteria enabled' flag. From my perspective, SSMS was designed by people who assumed the users would always be 'sa' or equivalent, and SQL Agent suffers similar design flaws since it does not allow fine-grained execution rights.
Actually, for Express and Standard, you're basically right. They're pretty much the 'small shop' or 'desktop' versions of SQL Server. As to MS implementation being godlike... yeah, no, I worked in SQL 7.0 and Access 2.0. I have no illusions about their ability to implement perfection. Just look at the number of reghack fixes for Windows 98 or ME for a perfect example of that... So, no, I'm not a fanboy. I do make a living off their products though so there will always be some bias into making it work.
As a confession, I am also a developer, but we don't have a "DBA" where I work and I'm the one who fixes things with the database, whether it is jobs or performance-related. Since it isn't a full-time responsibility, I am nowhere near as skilled as some on here, but would like to think my ability to think logically is as good as everyone else's. Also, as a developer, I tend to prefer locking down permissions to execute code using object-oriented security and manage security through the object graph.
Here you've got a leg up on me. It's been a long time since I did any heavy coding (VB6/C++) and I'm rusty, at best, and certainly not up to date on current methodologies.
You can't do this with SSMS, because it wasn't designed with fine-grained security in mind, and so it doesn't correctly address "interaction control" (making security user-friendly). Security isn’t about making all operations difficult; it’s about restricting access to operations with undesirable effects.
This may be a critical difference in perspective. SQL Server approaches security in three layers. Server, Development, and Data Access. As you mentioned above there were some security annoyances between versions, but the generalities hold.
The Server level permissions are granular to users where they opened up some options, like the new job usage/creation permissions. Otherwise, they are broad. This is primarily because once you start having to do that work you need a lot of things to make it happen. This is why that security is more role based then anything.
Development access are things like view definition on procs and the like. They're usually database related and can be fine controlled if necessary, but not usually. Data layer access and proc execution is usually much more finely controlled, and that's probably closest to your Object Oriented Security model, from what I read/understood from that site.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2010 at 12:09 pm
Craig Farrell (12/2/2010)
:blush: And I feel like an idiot for not taking the 3 minutes to keep reading the thread. I was under the same understanding as he was, since some of our DBAs here were intending to do the same thing eventually, and I'd assumed they'd done the research.Thank you for the correction.
It's OK - I should've been clearer and more considerate of speed readers.
Here you've got a leg up on me. It's been a long time since I did any heavy coding (VB6/C++) and I'm rusty, at best, and certainly not up to date on current methodologies.
Object-oriented security is an idea that has been around since the late 1960s, but it got a bad reputation in the 1970s, and was mostly ignored until the 2000s. It is slowly gaining recognition as the right way to secure distributed systems, where there is a combination of concurrency control and access control.
Thanks for your reply nonetheless. I guess if nothing I helped give you something to poke your DBAs with when you ask them how locking down SQL Agent jobs is going 🙂
Cheers,
Z-Bo
December 2, 2010 at 4:31 pm
I know I'm going to get flack for this, but it is a forum. Please assume that I know the benefits of scheduling everything you can in SQL Agent, there is plenty to be said for keeping it simple. I have also bumped into the problem of delegating job owners and editors to pretty much the same conclusion in several different environments. In most cases, adding the trustworthy users to the TargetServersRole role was enough to get by, but it never solved the problem completely. So that being said, I have a tirade to go along with this.
/* Begin Tirade */
To answer your actual question, no, I am not satisfied with the way SQL Server Agent works, but honestly, if it worked the way I wanted it to, it would be a separate product with a SQL backend divorced from the DB engine.
If "SQL Server Agent" were meant for developers it should have been called "SQL Server Developer Agent" or "Microsoft Enterprise Scheduler" It's my contention that SQL Server is not meant to be a scheduler for jobs that the DBA isn't responsible for or that don't directly affect the health and maintenance of SQL Server with very few exceptions. SQL Server Agent falls short of an actual enterprise class scheduler for several reasons. There are other reasons, but these are my top 5.
1) All jobs execute locally on the node hosting the SQL instance, this robs the node of resources that are used to service SQL.
2) Job coordination is limited to time, frequency, CPU idle and service start ... NOT inter-job dependency or extra-server signals. I know there are people out there who say you can create inter-job dependency by having one job spawn another, but try to have one job dependent on the completion of two others without creating schema or logic to support this.
3) Job notification relies on custom dbmail scripting or arduous operator setup which fall just outside of the DBAs core responsibilities (I really hate adding and removing recipients to notifications of custom jobs).
4) Error logging, and the sometimes the full text of the job output is truncated due to limitations in the space allocated to agent logging - finding failure messages is hard, and sometimes not possible. This causes lag between problem and resolution because the responsible parties don't have access to all the information without a lot of additional work.
5) And, the reason this post was written, security and ownership of jobs becomes too complex too quickly.
Unless you have a small shop, can fully trust non-dbas managing job schedules, operators, steps, and execution on the company's DATA STORE (not app server, or scheduler), or just have no other way of doing things, I would strongly recommend removing non-server related jobs from the production SQL instance and put them in an external scheduling environment where users can run amuck.
Options for your developers could be:
1) A SQL Instance dedicated to running jobs if you MUST use SQL Agent.
2) Use an actual enterprise scheduling software (they do exist, wikipedia has a good list of job scheduler softwares).
3) God forbid, use task scheduler (primitive, but it works)
4) They're devs, heck they could build their own
/* End Tirade */
December 2, 2010 at 4:54 pm
If only everything uncommented between /* Begin Tirade */ and /* End Tirade */ was actually executable and could be run to subvert the Master Control Program[/url] and return the power to the users[/url] 😉
You've got a lot of great points. Unfortunately, I am not in a position to make those recommendations to my coworkers. I push for too much change already. I also can't expand the budget or squeeze anything new into the budget, so I'd have to push for an unsupported open source solution, if anything, and the first question anyone would ask would be, Where have you used this before?
I'll add to the list:
6. SQL Agent doesn't actually have a flag that says whether a job is executing, and to derive this information you have to calculate it from the fact that the job has a start time but no end time.
7. You can't debug SQL Agent tasks directly
8. Scheduling doesn't allow object-oriented resource coordination, where task interdependencies are defined by more than just job steps and/or asynchronous sp_start_job invocations. For example, a job should not be allowed to start unless another job has completed, and the completed job should not be allowed to run again until the second job finishes. This is different from basic scheduling. (I agree that enterprise resource scheduling software handles this better.)
I also agree that it is weird for SQL Agent to be called an "agent", since to me, an Agent should not be coupled to the thing it is supposed to monitor.
December 2, 2010 at 5:13 pm
I hear you. I would probably break the "where have you used it before" into a multi part answer, and maybe a question.
SQL Server Agent does not meet the requirements of an enterprise level (or even business class) resource or job scheduler that they have given you. I would personally vouch for this as a DBA at a major corporation that does not use SQL Server Agent to schedule user jobs (including SSIS packages).
You will have to spend resources on developing workarounds to get SQL Server Agent to behave like a scheduler. I would personally vouch for this as a DBA who managed a datawarehouse entirely loaded by stored procedures orchestrated through custom schedulers and dependencies. Even if it didn't cost much to stand it up now, it will cost a ton to change it later.
You may have already spent resources on a dev and/or test environment (hopefully) which is a perfect place to test an open source scheduler - which would be a perfect answer to "where have you used it before?". My question is how could it possibly be worse?
December 2, 2010 at 5:32 pm
I think the reason for the only-sysadmins-can-edit-unowned-jobs limitiation is fairly well explained. Allowing non-sysadmin, non-owners to modify a job essentially gives them the ability to do anything the job owner has permission to.
If you want multiple developers to modify a jobs behavior, create a job that calls stored procedures and let them alter the stored procedure.
If you want to run jobs with interdependencies, create an SSIS package with all the precedence constraints you need and let the Agent job call the package. If you need more granularity, create a parent package that calls multiple packages.
You could create a job that reads package names from a table and runs them in order. The developers would have complete freedom to author packages and maintain the table.
So yes, the job-editing permissions in SQL Agent are restrictive, but that doesn't limit what you can do with a little imagination.
December 2, 2010 at 8:08 pm
I hate to pick apart a person's comments, but I used to think you could do everything with SQL Server, SSIS, creativity and custom code. Truth is you can, but there is a point where you reach diminishing returns, and the cost of working around a basic and undeniable problem is greater than just doing something else. I don't think there is a greater hidden costs than doing the wrong thing with the right intentions. Job scheduling is one of those things - it's way too easy to miss the big picture because it seems like such a simple thing. Maybe 80% of the time, rolling your own is the right, most cost effective, and easiest thing to do. Unfortunately, I can't afford to "rig it" in my environment, and this is what I've learned in my experience.
I think the reason for the only-sysadmins-can-edit-unowned-jobs limitiation is fairly well explained. Allowing non-sysadmin, non-owners to modify a job essentially gives them the ability to do anything the job owner has permission to.
Agreed, this is a good reason to use an external scheduler that runs jobs under the context of a specified login object controled by a scheduling administrator ... not a SQL DBA (unless of course the DBA is the scheduler admin - if that's the case, it's nice to have the tools to do both the right and most efficient way).
If you want multiple developers to modify a jobs behavior, create a job that calls stored procedures and let them alter the stored procedure.
This works, but they cannot execute, start or stop the job, modify the schedule, alter the notification or set dependencies. The stated problem is not modifying what a job does, but when and where it does it and who gets notified and what happens next. These are core features of a true enterprise scheduler, not a bolt-on agent.
If you want to run jobs with interdependencies, create an SSIS package with all the precedence constraints you need and let the Agent job call the package. If you need more granularity, create a parent package that calls multiple packages.
You are right, this does work. Controller packages have great flow control and flexibility with precedence. But SSIS packages do not solve any shortcomings of SQL's inherent job scheduling only task coordination. You can schedule the execution of this controler package from any scheduler, you don't have to use SQL Server Agent on a production SQL Server to do it. And if you want your ETL admins to have any control over starting/stopping/debugging/rescheduling/coordinating and logging of this package, you should put it in a place where they can control the schedule. Otherwise prepare to be the ETL admin.
You could create a job that reads package names from a table and runs them in order. The developers would have complete freedom to author packages and maintain the table.
So yes, the job-editing permissions in SQL Agent are restrictive, but that doesn't limit what you can do with a little imagination.
Yes, this is absolutely right. You have to roll your own scheduling and dependency system in order to build in just a little of the flexibility of an enterprise scheduler.
Your points are not lost, I don't dispute any of it. Creativity is probably the best extension to any systems basic features, and you should always try to work with what you have. I also think that the effort put into making SQL Server and SQL Server Agent work as an enterprise job scheduler is short sighted and it is not an experience I would wish on anyone, especially having done both.
December 2, 2010 at 8:26 pm
gklundt (12/2/2010)
If you want multiple developers to modify a jobs behavior, create a job that calls stored procedures and let them alter the stored procedure.
This works, but they cannot execute, start or stop the job, modify the schedule, alter the notification or set dependencies. The stated problem is not modifying what a job does, but when and where it does it and who gets notified and what happens next. These are core features of a true enterprise scheduler, not a bolt-on agent.
Right. If you are changing stored procedures, then you probably also want to check them into source control as well. Now you have a software configuration management problem where you need to synchronize between the source control subsystem and the deployment of stored procedures and the system schedule.
Ideally a scheduling system would simply handle the composition and coordination of software components already deployed through your software configuration management system, which in turn only deploys code from your trunk in version control. The scheduling system should only apply scalar values as parameters to the software components. And it would log the changes to the schedule for you as a separate subsystem with fancy ITIL-compliant or whatever auditing controls.
Ah, that would be a great place to be. 🙂
December 2, 2010 at 9:01 pm
ITIL? Source control? Now that's just crazy talk. You can't get them to invest in something FREE.
Heck, I just would like user jobs to run on a schedule and have content owners detect and research problems and even restart and schedule them without my involvement. I'd rather be working on real database issues and maintenence, like solving ways to get around crappy schedulers and making horrible queries run well ... I mean establishing baselines and analyzing metrics to decrease risk, maximize the lifespan of the existing hardware and charting scalability plans, patching servers, maintaining security and backups, and generally protecting the integrity of the company's data against dumb ideas.
No, someone would rather have me spend my time figuring out ways to make SQL do what SQL isn't coded to do (in effect increasing risk and shortening the lifespan of existing hardware ... at least it's a reason for backups).
<you might be able to tell, this is a hot button issue with me>
December 2, 2010 at 9:12 pm
Yeah, it's okay. I don't enjoy when people ask me why X is taking so long when X is completely stupid and we shouldn't be doing X. Then after they see how many workarounds it took to do X, they come to their senses and see X was a bad idea after all and we should be doing Y.
I wish I wouldn't waste creative energy doing ridiculous things. I have a couple of ideas for open source projects for improving life for many DBAs, but I waste so much creative energy at work that I never get around to it in my free time. Its just mentally zapping.
Stay strong.
"He who does not fall does not stand!" - Fedor Emelianenko
February 22, 2012 at 6:00 pm
This problem would be solved if Jobs could be owned by a Group rather than individual. Its ridiculous that developers can't edit each others jobs (in a dev env). Developers in my company are not SA so we are extremely limited by SQL Server security. Its just not good enough.
June 4, 2020 at 10:25 pm
use 3 party apps like autosys, db engine is for db engine purposes only not app jobs.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply