The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)

Question

I have installed SQL Server 2016 Express edition and also installed SSMS 18.4, now I am having an issue while restarting the SQL Server Agent service.

I am able to login with my system user, but not able to login with the default user name like: dot (.) or (local) or sa account.

Blastoderm

Answer 1

I had the same problem on a local instance of SQL Server 2017 developer edition. For me the solution was:

• go to services.msc

• locate SQL Server Agent service, right click properties, click Log On tab and take note the user that is running the service**. for me it was
  Local System account (the default). back out of the dialog

• go to to SQL Server Management Studio, login, open the Security node, open Login, and locate the user running the service. In my case the
  user is [NT AUTHORITY\SYSTEM] which correlates to Local System above. right click the user, properties, Server Roles, add sysadmin priv and save.

• start SQL Agent

**The user I granted sysadmin to was not the same user as the SSMS was logged in as, it was the user that was set to run the service

Pantin

Answer 2

I managed to resolve this error by performing the following steps:

1. Run Services.msc.
2. Locate the SQL SERVER AGENT service and go to properties
3. Change Login to Local Account

Now, for SQL Managmento Studio, connect to your bank server and perform the following steps:

1. Locate security and server roles
2. Click sysadmin
3. Add your logged in user (computer name)(user name)

Gamelan

Answer 3

Check if you have the default account NT SERVICE\SQLSERVERAGENT with sysadmin rights within SQLServer, if it exists is not necessary to grant permission to user who start the service, otherwise you must to grant sysadmin permission to account who start the SQLServer Agent service.

Learned