Labels

SQL Server agent job schedule modification by non-admin permission





Only members of sysadmin can modify a schedule owned by another user.

By default, members of the sysadmin fixed server role can execute the stored procedure to change schedule. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:


  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole


For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Solution: Need to grant the particular owner login with schedule update permission with the below query.

USE msdb ;  
GO  

/***********************************/
EXEC dbo.sp_update_schedule  
    @name = 'JOBNAME',  
    @enabled = 0,  
    @owner_login_name = 'LoginName' ;  
GO 

/***********************************/

Note: If user is non admin and granted all the above permission but being a job owner he cannot modify the job schedule as he will get the below error



No comments:
Write comments

Please do not enter spam links

Services

More Services