Could not autostart SQL Server agent when SQL Server starts

Question

Could not autostart SQL Server agent when SQL Server starts

Nirmala-369598

Say Hey Kid

Points: 665
More actions
November 21, 2006 at 4:02 am

#121580

I have created a Stored Procedure to autostart SQL Server agent when SQL Server starts.
The stored proc is
CREATE PROC AutoStart_SQLAgent
AS
BEGIN
DECLARE @Err int, @Msg varchar(100), @ServiceName sysname
SET @ServiceName = 'SQLServerAgent'
EXEC master.dbo.xp_servicecontrol 'START', @ServiceName
SET @Err = @@ERROR
IF @Err = 0
  BEGIN
   RAISERROR ('Successfully started SQL Server Agent', 1, 1) WITH LOG
  END
  ELSE
  BEGIN
   SET @Msg = 'Error occured while starting SQL Server Agent. Error code: ' + STR(@Err)
   RAISERROR (@Msg, 18, 1) WITH LOG
  END
END
GO
EXEC sp_procoption 'AutoStart_SQLAgent', 'startup', 'true'
GO
The procedure has been successfully created and when i executed , the error is
StartService() returned error 5, 'Access is denied.'
Successfully started SQL Server Agent
Msg 50000, Level 1, State 1
Any body knows the solution ,Pls help me.
Thanks
Nirmala

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

I'd say that your SQL Server account does not have permissions to start services. Under what account are you running the MSSQLSERVER (or similar) service?

Nirmala-369598 Say Hey Kid Points: 665 More actions · Answer 2

Thanks for responding.

I think SQLServer account is, Network Service account (NT AUTHORITY\NetworkService).

If this would be the problem, what should i do to resolve it. Pls helpme.

Once again thanks,

Nirmala

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

You need to get privilege to do it. Or you may ask sa to do it.

You can simply check the proper checkbox in the properties of your SQL Server.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Why are you doing this? Just set the SQL Server Agent service to automatically start. It's dependent on the SQL Server service, so the agent one won't start until the main one does.

-SQLBill

Nirmala-369598 Say Hey Kid Points: 665 More actions · Answer 5

Thanks for your reply.

May i know whick checkboxes have to be enabled and could u explain in detail.

Thanks

Nirmala

Suresh Bose SSC Veteran Points: 213 More actions · Answer 6

I think you need to check the user's with DB mappings.

THanks,

S Suresh

Michael Piper SSC Journeyman Points: 80 More actions · Answer 7

Here's how to do it from Enterprise Manger if you have the access privileges:

Open Enterprise Manager
Right Click on the SQL Server
Select Properties
Select the "Autostart SQL Agent" option on the General Tab
Click "OK" to accept the settings and close the Properties Dialog box

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Michael,

Autostart in that context only occurs when the server is started. Then SQL Server Agent will autostart.

I believe the poster's problem is that when you just stop the services (MSSQLServer and SQLServerAgent); restarting MSSQLServer does not start SQLServerAgent. You must manually restart SQLServerAgent. However, if you start SQLServerAgent, it will start MSSQLServer since it depends on that service.

But as I said, you must do that manually unless you are restarting your server or you have figured out a script to start it.

-SQLBill

Nirmala-369598 Say Hey Kid Points: 665 More actions · Answer 9

Hi

I need to autostart the SQLServer Agent thro script(shown in earlier post) and i get error.

Also i couldn't able to find the option "Autostart SQL Agent" what u have mentioned in your post. Iam using SQLServer 2005 and i work in SQLserver management studio.What i should do to solve the problem.

Thanks

Nirmala