Today, we found a case that our customer reported the following errors "Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1 [Batch Start Line 0] The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OASetProperty, Line 1 [Batch Start Line 0]" Following I would like to share my lessons learned about these error messages.
Understanding the EXEC Permissions Error:
The error message you encountered is related to the permissions required to execute certain stored procedures, specifically 'sp_OACreate' and 'sp_OASetProperty.' These stored procedures are part of the 'sys' schema in the 'mssqlsystemresource' database. The error message "The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'" indicates that the user or role attempting to execute the query does not have the necessary permissions to use these stored procedures.
Azure SQL Database and Azure SQL Managed Instance:
Ole Automation, for example, 'sp_OACreate' and 'sp_OASetProperty' are not supported in Azure SQL Database and Azure SQL Managed Instance, there are alternative approaches available to achieve similar functionalities within the cloud environment. Azure provides various platform services and integrations that can be utilized securely and reliably. URL: Compare the database engine features of SQL Database and SQL Managed Instance - Azure SQL Database &...
For instance, Azure Functions, Logic Apps, or Azure Data Factory can be leveraged to interact with external components and services, offering controlled and secure execution.
