SQL Server - Permissions for Using OLE Automation (Yes, Documentation is Wrong)

Views: 682
Reading Time: 4 minutes

Speak peopleALL !!
In this very simple and quick article, I will share with you which permissions to use OLE Automation, a feature of SQL Server that allows you to use DLLs and Windows APIs through calls from Stored Procedures to perform various tasks within the database, such as File Operations, use regular expressions (RegExp) and even, perform web requests.

Introduction

As I had commented in the post Introduction to SQL Common Language Runtime (CLR) in SQL ServerThe big problem with OLE Automation is that by enabling this feature, any user (with permission) can create anything from it, since commands are unmanaged and run within the SQL Server process. In the event of a memory leak failure (which is not so uncommon), the instance can be stopped as the SQL Server process is automatically closed by the operating system (!!!!)

Also, accurate documentation about OLE Automation is difficult to find and it is quite complex to create routines using this feature. For this reason, I recommend replacing OLE Automation routines with SSIS packages and, if not possible, routines using SQLCLR, as I have already described the advantages in the article above.

Permissions to use OLE Automation

If you really have a very specific need that requires the use of OLE Automation (and you don't want to use SQLCLR), you may have read the Microsoft documentation and in several other Brazilian and American blogs, which indicate that, to use OLE Automation procedures (sp_OA *), you must belong to the server role sysadmin, correct? And then you have to release sysadmin permission so the user can create the objects or just deny that request, right?

English version:

Portuguese version:

All documentation pages for these sp_OA * procedures have this same permission requirement (sysadmin). But what if I tell you that CAN'T Is it true and is it very easy to prove it? 🙂

The first step to this is Enable OLE Automation on Instance:

If you do not do this, you will come across this error message:

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1 [Batch Start Line 0] SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Now let's create a new user to test if I can use OLE Automation without being in server role sysadmin:

And now, connected with the test_OA user, let's try to try using OLE Automation with the code below:

And while trying to run, we get the following message:

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'.

Well, solving this permission problem is VERY simple and doesn't require the user to be in role sysadmin:

And now, trying to rerun the above script:

SUCCESS! We were able to run the OLE Automation script normally, with only the necessary permissions.

I hope you enjoyed this article and see you next time.
PS: You can let me send a documentation correction request 🙂

Corrected Documentation - “Mother, I'm on Globo!”