In the last week or so, we've been tightening up the security on our SQL boxes, and removing as many logins with sysadmin access. This has included various of the service accounts.
On pretty much every single SQL 2012 box, this has had an unintended consequence: the SQL Agent Jobs have suddenly stopped running. Moreover, if you start a SQL Agent job, it looks like it is running, but, then, hit close - and actually it's done nothing at all. Until you look in the SQL Agent Error Log.
A selection of errors:
Date 21/02/2014 08:14:38
Log SQL Server Agent (Current - 21/02/2014 09:45:00)
Message
[298] SQLServer Error: 229, The SELECT permission was denied on the object 'sysjobschedules', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (SaveAllSchedules)
Date 21/02/2014 09:45:31
Log SQL Server Agent (Current - 21/02/2014 09:45:00)
Message
[298] SQLServer Error: 229, The SELECT permission was denied on the object 'sysjobservers', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)
Date 21/02/2014 09:45:31
Log SQL Server Agent (Current - 21/02/2014 09:45:00)
Message
[298] SQLServer Error: 229, The UPDATE permission was denied on the object 'sysjobservers', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)
Date 21/02/2014 09:45:31
Log SQL Server Agent (Current - 21/02/2014 09:45:00)
Message
[298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_log_jobhistory', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)
Note: just giving the SQL Agent account db_owner permissions on msdb didn't work. I just got different errors:
Date 20/02/2014 09:17:24
Log SQL Server Agent (Current - 20/02/2014 09:17:00)
Message
[298] SQLServer Error: 14262, The specified @job_id ('CF59EAF1-FAEC-4E5B-BE53-D07FE2CD9F60') does not exist. [SQLSTATE 42000]
Date 20/02/2014 09:17:24
Log SQL Server Agent (Current - 20/02/2014 09:17:00)
Message
[000] Unable to retrieve steps for job SQL Backup full backup: master and 5 others (created 05/07/2013 13:06:40)
(Yes, we use Red Gate SQL Backup here. And jolly good it is too).
Giving it additional SQL Agent Operator rights, however, did work...and was rather less sledgehammery in its approach than giving it sysadmin rights. If using Red Gate SQL Backup, you will need to ensure the account can execute the stored procedures in master. If using Ola Hallengren's Backup Solution (we use that too), then the only answer is, in fact, to grant sysadmin access - otherwise the SQL Agent Account cannot create the relevant folders. If you have regular checkdb jobs running, then you need to make sure it can execute DBCC CHECKDB on all databases.
Subscribe to:
Post Comments (Atom)
I have one SQL 2008 box with the same problem. I have been going over some of the security on the servers and so far the SQL servers have been giving me fits. I hate having to go behind what someone else did to make it right. I change the permissions for the domain user that is also running the SQL Server services and took away sysadmin rights and gave the user additional SQL Agent Operator rights on the MSDB database and I still got the errors. It didn't work properly until I gave it sysadmin again. What else could I do?
ReplyDelete