Skip to content

How to allow SQL Server Agent to be run

August 5, 2014

If you receive the error message “Ad hoc update to system catalogs is not supported” when trying to activate the SQL Server Agent and the message “SQL Server blocked access to procedure ‘dbo.sp_sqlagent_get_startup_info’ of component ‘Agent XPs'”
You need to run the following commands to fix the issue

Allow updates is used to set up direct ad-hoc updates to system catalogs and tables. This is set to default to 0, but for some instances, it may have been set to 1 beforehand, so here you are setting it back to 0.

EXEC sp_configure ‘allow updates’
GO
RECONFIGURE
GO

You can then enable the use of Agent Xps by using sp_configure because SQL Server blocked access to procedure ‘dbo.sp_sqlagent_get_startup_info’ of component ‘Agent XPs’ because this component is turned off due to security. You can enable it by the following:-

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE
GO

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: