Post reply

Serious Problem with SQL Agent and Permissions

  • November 26, 2006 at 5:02 pm

    #175035

    Hi All,

    I have assigned a user permissions to be a db_owner on the msdb database and the user is also part of the following msdb roles: Lets assume that the user is called UserA and user A belongs to RoleA, RoleA is also db_owner

    They also belong to the following roles on Msdb, and yet they can only view jobs they dont own, but I want them to be able to alter schedule for any job which sa owns, without giving them sa rights.

    ·  SQLAgentUserRole

    ·  SQLAgentReaderRole

    ·  SQLAgentOperatorRole

    But I notice that the user can log into the system, but they cannot change the schedule for a job, they can only view it. Although the job is not owned by the user, but by SA, it leads me to ask, does the owner of every job have to be changed in order for them to alter the schedule.

    I was wondering if there was a way around this without giving the user Sysadmin rights, also its not just only one user that should be able to change the jobs, it is all users who belong to RoleA.

    I look forward to hearing from you.

    Thanks

     

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • November 26, 2006 at 5:25 pm

    #673961

    If I recall correctly, SQL will use the most restrictive of the roles if a user is in more than one. Try just putting them in the Agentoperator role and removing them from the reader/user role. Lemme know if that works!

    Thanks

    Rich

  • November 27, 2006 at 3:07 am

    #673997

    Thanks for your reply, from what I read and understand, If you assign users the Agentoperator  role, they automatically inherit the role of the other 2, so basically the Agentoperator  is the most previledged, but it would inherit the properties of the previous 2. see:

    http://msdn2.microsoft.com/en-US/library/ms188283.aspx

    I have tried it, didnt work. Thanks for your reply though

    I think it might be to do with proxy settings, i.e setting up a proxy account or so. I am not sure about this and havnt tried it before, but I have found the following sites, dont quite understand thier approach to the problem:

    http://www.databasejournal.com/features/mssql/article.php/3488046

    http://msdn2.microsoft.com/en-us/library/ms190698.aspx

    http://msdn2.microsoft.com/en-us/library/ms187901.aspx

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • November 27, 2006 at 4:37 am

    #674002

    Doh, I recalled incorrectly- they will indeed assume the most priviledged role.Sorry. anywho, according to those pages none of those roles can edit schedules of jobs that do not belong to them. Only the Sysadmin role can do that. The operator role can only disable or enable jobs and schedules that do not belong to them with the use of sp_update_job/sp_update_schedule.

    Thanks

    Rich

  • November 27, 2006 at 3:23 pm

    #674188

    I have heard things about proxy configuration, is this going to help in this case ?

    I am thinking there must be a way of using the GUI

    Thanks

    Kindest Regards,

    John Burchel (Trainee Developer)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply