AFAIK, it only helps against hackers/insiders who steal the raw file. If you provide access to your bucket, it isn't doing anything. As always with Amazon (and other services, really): you really should understand the service. Just using one because you read "encryption" is unwise.

It's all about layers of protection.

This layer of server side encryption is designed to protect against an attack where someone breaks into an AWS data center, pulls a disk drive out of a storage system that is hosting S3 objects, and then tries to read the data off of that disk drive. Without the key (which is obviously stored separately, on a separate system) the disk will be useless to them.