Sometimes, when we are working on the test environment, our SQL Server agent will not work, and it will show the error as Access Denied.
In this tutorial, I will show you the steps to solve this issue in SQL Server Management Studio. Let’s get started to see the troubleshooting methods.
SQL Server Agent is Missing
Generally, an SQL Server Agent is a Microsoft Windows service that executes scheduled admin tasks, which are technically called jobs in SQL Server. The agent will help you improve the database performance and ensure the SQL environment works smoothly.
Sometimes, we cannot see the SQL Server Agent itself in the object explorer before solving the issue.
Below are the steps to get the SQL Server Agent.
- Open SQL Server Management Studio, expand the Logins and click your login name.
- Now Login Properties – imsdbusr page will open. Click User Mapping, simultaneously tick the checkbox msdb, and click OK.
- Make sure db denydatareader is unchecked. If it is checked, then uncheck and click OK on the same page.
- Check now whether the SQL Server Agent option is visible. If not, tick the db owner checkbox and click OK.
- Once you click OK, it should be visible. If not again, click your login name and click Server Roles, then tick the checkbox sysadmin and click OK.
By doing this, you can get the SQL Server Agent in SQL Server Management Studio.
SQL Server Agent not Starting
- If you are using the SQL Server Express Edition, SQL Server Agent will not start.
- To know sql server version, use the below command.
SELECT SERVERPROPERTY('Edition')
- Another reason could be a corrupted or failed SQL Server installation. You can try to repair the installation or reinstall it.
- If you have changed the password you use to log into the database, you must also modify the properties of the SQL Server Agent service and update the password there.
- Make sure your Windows Firewall is not blocking it. If it is, add an exception to the firewall to run the SQL Server Agent service.
Troubleshooting:
If you want the SQL Server Agent to start automatically, you have to change its properties in Windows Services. It is possible that the SQL Server Agent is not set to start automatically.
In this section, we will learn how to change the SQL Server Agent properties so that it is automatically started.
- Press Windows Key+R to open the Run utility. Type services.msc to open the Windows Services.
- Right-click SQL Server Agent and click Properties.
- Choose Startup type as Automatic and click OK.
Now, the next time you restart your computer, the SQL Server Agent should start automatically.
Conclusion
This tutorial might help you understand why SQL Server agents are not showing up and not starting in the SQL Server management studio. Try from your side and see the changes.
You may like the following SQL Server tutorials:
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
