Error “Alter failed for Server ‘[servername]’ “in maintenance plan’s “check database integrity task”

This error happened in one of the SQL2005 SP4 instances I am managing. The root cause of this issue is that the integrity check task runs statement ‘EXEC sys.sp_configure N'user options', 0 RECONFIGURE‘ in the background and this statement conflicts with another “allow update” (with value of 1) setting to system catalogs.

To put it another way, if your “sp_configure 'allow update'” is valued at 1 (meaning system catalogs can be updated) and you run “sp_configure N'user options', 0 RECONFIGURE” (which will try to update system catalogs), an error message will be reported: Ad hoc update to system catalogs is not supported.

However, in SQL2005 and newer, updates on system catalogs are not possible anymore, so the false setting of ‘allow update’ to 1 is meaningless and is causing unexpected troubles like this. The solution is very simple: just configure the ‘allow update’ option from value 1 back to 0 which it should be by running “sp_configure 'allow updates', 0 reconfigure“. Also another good practice is to disable ‘allow update’ option for all 2005+ instances.