Once we migrated to the SQL 2008 R2 was getting the following error
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
I had to do the following to resolve this
The following example shows how to view the current setting of OLE Automation procedures.
Copy
EXEC sp_configure 'Ole Automation Procedures';
GO
The following example shows how to enable OLE Automation procedures.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
http://msdn.microsoft.com/en-us/library/ms191188.aspx
