Menu
Is it possible to enable or disable the sql server agent using t-sql?
600
Go to the Job Activity Monitor and ctrl-left click on each, then right click and select disable job, or you can write a script against msdb. dbo. sysjobs and update the enabled column.
There are only two ways that someone can have permission to execute a SQL Agent job. You must either own the job, or be a member of the role SQLAgentOperatorRole (found in msdb). Unfortunately SQLAgentOperatorRole grants permissions to run any job (among other things).
To configure SQL Server AgentIn Control Panel, select System and Security, select Administrative Tools, and then select Local Security Policy. In Local Security Policy, select the chevron to expand the Local Policies folder, and then Select the User Rights Assignment folder.
Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 0 --Disable exec msdb..sp_update_job @job_name = 'Job Name', @enabled = 1 --Enable You can use this to set it ON or OFF
EXEC xp_servicecontrol N'stop',N'SQLServerAGENT' EXEC xp_servicecontrol N'start',N'SQLServerAGENT' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
