 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / January 2008SQL job owner and right
Hi, A user has right to create and operate a SQL job, but only limited to the jobs that this user is created. Without granting sysadmin right, how to grant a user to be able to operate and change the job which is created by other users? Thank you.  SignatureBest Regards, Lynn If you are using SQL2000 then you need to be sa. In 2005 there are 3 new roles that define the abilities of non-owners in SQL Agent. Check out these in BooksOnLine for more details: SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hi, > [quoted text clipped - 4 lines] > > Thank you. We are in SQL 2005 and my account is already granted all those three roles and account A also has these three roles. But, I can't see the job that account A created. What's the best practice to archive -- multiple accounts can manage SQL job without sysadmin right? Thank you again. SignatureBest Regards, Lynn > If you are using SQL2000 then you need to be sa. In 2005 there are 3 new > roles that define the abilities of non-owners in SQL Agent. Check out these [quoted text clipped - 14 lines] > > > > Thank you. Sorry. I just confirmed that we only have SQLAgentUserRole. That's the reason I couldn't see the job which is created by account A. Again, more than 2 people will manage same jobs for application, but not the jobs related to server maintainance. Reader and Operator role won't fulfill this right as well. What's the best practice for this situation. Thank you again. SignatureBest Regards, Lynn > We are in SQL 2005 and my account is already granted all those three roles > and account A also has these three roles. But, I can't see the job that [quoted text clipped - 23 lines] > > > > > > Thank you. By "Operate and Change" the job do you mean edit what the job does or just when it runs? If you want to be able to edit other peoples jobs you have to be sa. Otherwise you run the risk of someone who is not able to access or modify data being able to do so thru editing a job that is owned by someone who is authorized. That defeats the purpose of the security roles altogether. 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. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Sorry. I just confirmed that we only have SQLAgentUserRole. That's the > reason I couldn't see the job which is created by account A. [quoted text clipped - 41 lines] >> > > >> > > Thank you. Andy, A Windows Group cannot own a job. It is the wrong type of account. The job owner must be either a SQL or a Windows Login. I have read (and guess that I basically understand) the reasons why this is necessary for changing security context. You see the same behavior that EXECUTE AS cannot be a group. But it would be nice to have groups own things like jobs. I have some jobs owned by service logins, e.g. 'DepartAJobOwner' and those who can login with that account can manage those jobs. RLF > By "Operate and Change" the job do you mean edit what the job does or just > when it runs? If you want to be able to edit other peoples jobs you have [quoted text clipped - 52 lines] >>> > > >>> > > Thank you. Andrew, Thank you for your suggestion. I added a window group in SQL security and try to assign the job owner to this group, but the window group is not allowed to be the owner of the job by design. I got the error message when I assign group to job owner The specified'@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]). Please help. Thanks. SignatureBest Regards, Lynn > By "Operate and Change" the job do you mean edit what the job does or just > when it runs? If you want to be able to edit other peoples jobs you have to [quoted text clipped - 52 lines] > >> > > > >> > > Thank you. Yes Russell just corrected me on that. I never tried that but at the time it seemed a perfect solution:(. Russell did offer a compromise I suppose. If you create a Windows login that each of these people can use when editing jobs you can make it the owner. But you still have the same issue of security that I mentioned earlier. If you trust these users enough to let them do anything they want to any of those jobs then you can trust them to login with that account when editing jobs. Otherwise they can only edit their own. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Andrew, > [quoted text clipped - 78 lines] >> >> > > >> >> > > Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.