Post reply

How to assign permission for creating and scheduling SQL job in SQL 2005

  • August 12, 2010 at 3:38 pm

    #239017

    Hi:

    I'm looking for a solution about SQL 2005 Job creation permission assinging to a user.

    I'm the administrator of a production server where 6/8 applications and databases are running. I'm the administrator of both server and SQL db. For up and running all projects I created logins and made owner of their own db as requested. Now for few projects it's necessary to create and modify SQL jobs. It's not expected that more than one user should have sysadmin access to the database.

    In that case how can I give those users permission to create and modigy SQL job agent?

    Thanks in advance.

    Regards,

    Maksuda...

  • August 13, 2010 at 1:25 am

    #1206440

    See SQL Server Agent Fixed Database Roles in msdb database.

    SQLAgentUserRole

    SQLAgentReaderRole

    SQLAgentOperatorRole

    You will get all details in the link below

    http://msdn.microsoft.com/en-us/library/ms188283.aspx

  • August 13, 2010 at 7:50 am

    #1206639

    Thank you very much Vivek. Link is really helpful.

    Maksuda...

  • September 2, 2010 at 8:14 am

    #1215635

    Months before I posted this thread and Vivek posted this url for to understand the permission on SQL job:

    http://msdn.microsoft.com/en-us/library/ms188283.aspx

    URL was informative.

    I didn't get chance to work with the roles.

    Yesterday when I was trying what they said on the url I found that my permission on SQL Server Agent Fixed Database Roles is not working. Under Database role membership for msdb, I checked the appropriate SQL Server Agent fixed database role (SQLAgentUserRole). Users can view the SQL job but can't update or modify anything. I'm wondering is there anything I need to do other than giving permission to the role for update or modify the SQL Job.

    Any help is highlt appreciated.

    Thanking You

    Maksuda...

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

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