Error : The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

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