Of course you don't see any changes since the settings are set to Automatic Brightness. That means, any manual changes are disregarded. Or am I missing something?
I've just tested it on a HTC Desire HD (2.3.5) and the screen brightness DOES change for about a second, but then it's immediately re-adjusted/overwritten by the automatic brightness. However, if you uncheck Auto Brightness option in the settings, you can see that the value has changed correctly.
So, imo everything is working as expected. If you want your changes to take effect you might have to disable Auto Brightness first:
Settings.System.putInt(getContentResolver(), Settings.System.SCREEN_BRIGHTNESS_MODE, Settings.System.SCREEN_BRIGHTNESS_MODE_MANUAL);