Strange one today. A dev came over with a strange question. He was trying to enable CLR on his local SQL instance but getting an error when trying to do so. Curious I thought, so I took a walk over to his desk to see what was going on.
He was executing a basic sp_configure script to enable CLR, nothing special, no frills.
When he ran this he was getting an error:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Most curious. I went back to my machine and wasn’t able to reproduce the problem. Rather than have him attempt to restart his instance I just had him reconfigure with override
This time it ran correctly and gave a good result.
All the “WITH OVERRIDE” does is force SQL to be reconfigured with the updated value (unless it would cause a fatal error). Something to remember if this ever crops up for you.