What's an example of an exploitation of the security from enabling Ole Automation Procedures Server Configuration option?
I don't think I would look at it in terms of exploitation. It's very similar to (if not the same security issue as) xp_cmdshell. These are wide open in terms of what you can execute if they are enabled, and for processes that reach outside of SQL Server, they use the security context of the service account running SQL Server (although xp_cmdshell does at least have the option to set up a proxy account for non-sysadmin users).

But, you need to be in the sysadmin instance-level role in order to execute the sp_OA* procs (or be given explicit EXECUTE permission), so you can at least keep all uses of it within the confines of stored procedures that use Module Signing to get the elevated permission (yes, specifying WITH EXECUTE AS N'dbo' also works, if the Login associated with the dbo User is in the sysadmin role, but Module Signing is better).

Looking at the CIS quote in @kevinsky's answer (i.e. "These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server."), unless I am missing something, that seems to be an overblown, or possibly uninformed, reaction. It would certainly be a huge issue if all users had access to it if it were enabled. But really, it's only users that already have the ability to enable "OLE Automation procedures" that are allowed to use the sp_OA* stored procs. I'm not saying that it should be enabled if it isn't being used, but this doesn't seem to be an inherent security risk.

I think the security "issue" is that it allows for code to be created by developers that either does things that you might not approve of on the network or file system or at the OS level, or perhaps there are unintended consequences.

That being said, if you are asking with regards to legacy code that you need to support, then while not ideal, it will probably work. But, if this is in regards to new development then you shouldn't be using OLE Automation; you should instead be using SQLCLR. SQLCLR has many benefits over OLE Automation, including (but not limited to):


Better security: you can restrict code to being confined to SQL Server internally (SAFE) or able to access external resources but not alter the system or do asynchronous work, etc (EXTERNAL_ACCESS)
Ability to impersonate caller's Window's login when accessing external resources (not confined to service account)
Better datatype support: OLE Automation should be COM interface, and it doesn't support MAX types, XML, any of the more recent types.
Better memory management