SQLSERVERAGENT service fails to start , SQLServer Error: 229

Issue

The SQL agent services fail to start and give the following error when we try starting it from the services console :

When we attempt to start SQL agent services we get the following error :

---------------------------

Services

---------------------------

The SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

---------------------------

OK

--------------------------

Environment

Windows 2003

Service Pack 2

SQL Version: SQL2000 Enterprise Edition

SQL Service Pack: RTM

SQL Build: 8.00.2039

SQL Platform: x86

SQL Server Name: HRSERVER

SQL Instance Name: default

· We collected the SQL agent logs and saw the following errors

2010-10-24 16:51:19 - ! [298] SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_has_server_access', database 'msdb', owner 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

2010-10-24 16:51:19 - ! [298] SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_get_startup_info', database 'msdb', owner 'dbo'. [SQLSTATE 42000]

2010-10-24 16:51:19 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID

2010-10-24 16:51:19 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID 3696

2010-10-24 16:51:19 - ? [101] SQL Server version 8.00.2039 (0 connection limit)

2010-10-24 16:51:19 - ? [102] SQL Server ODBC driver version 3.86.3959

2010-10-24 16:51:19 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is

2010-10-24 16:51:19 - ? [310] 8 processor(s) and 2048 MB RAM detected

2010-10-24 16:51:19 - ? [339] Local computer is HRSERVER running Windows NT 5.2 (3790) Service Pack 2

2010-10-24 16:51:19 - ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role

2010-10-24 16:51:19 - ? [098] SQLServerAgent terminated (normally)

· Just a test changed the startup account from the local system to a domain account and subsequently added it to the sysadmin server role which enabled SQL agent services to start up

· SQL server services inspite of having local system account as its startup , was working fine but SQL Agent on the other hand failed to startup with local system account

· We then Checked the following permissions for the service account under secpol.msc (local security policy)

  • Act as Part of the Operating System

  • Bypass Traverse Checking

  • Lock Pages In Memory

  • Log on as a Batch Job

 • Log on as a Service

 • Replace a Process Level Token

· We added the "SYSTEM" account to all these permissions and we were able to start it up successfully

Regards,

Amrutha