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 NO Is it true and is it very easy to prove it? 🙂
The first step to this is Enable OLE Automation on Instance:
|
1 2 3 4 5 6 7 8 9 10 11 |
sp_configure 'Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ole Automation Procedures', 1 GO RECONFIGURE GO |
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:
|
1 2 3 4 5 6 7 8 |
USE [master] GO CREATE LOGIN [teste_OA] WITH PASSWORD='123', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, DEFAULT_DATABASE=[master] GO CREATE USER [teste_OA] FOR LOGIN [teste_OA] GO |
And now, connected with the test_OA user, let's try to try using OLE Automation with the code below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
DECLARE @strArquivo VARCHAR(255) = 'C:\Senha muito importante.txt' DECLARE @hr INT, @objFileSystem INT, @objFile INT, @ErrorObject INT, @ErrorMessage VARCHAR(255), @Path VARCHAR(255),-- @ShortPath VARCHAR(255), @Type VARCHAR(100), @DateCreated DATETIME, @DateLastAccessed DATETIME, @DateLastModified DATETIME, @Attributes INT, @size INT SET NOCOUNT ON SELECT @hr = 0, @ErrorMessage = 'opening the file system object ' EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr = 0 SELECT @ErrorMessage = 'accessing the file ''' + @strArquivo + '''', @ErrorObject = @objFileSystem IF @hr = 0 EXEC @hr = sp_OAMethod @objFileSystem, 'GetFile', @objFile OUT, @strArquivo IF @hr = 0 SELECT @ErrorMessage = 'getting the attributes of ''' + @strArquivo + '''', @ErrorObject = @objFile IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'Path', @Path OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'ShortPath', @ShortPath OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'Type', @Type OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'DateCreated', @DateCreated OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'DateLastAccessed', @DateLastAccessed OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'DateLastModified', @DateLastModified OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'Attributes', @Attributes OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'size', @size OUT IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @ErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @ErrorMessage = 'Error whilst ' + @ErrorMessage + ', ' + @Description RAISERROR (@ErrorMessage,16,1) END EXEC sp_OADestroy @objFileSystem EXEC sp_OADestroy @objFile SELECT [Path] = @Path, [ShortPath] = @ShortPath, [Type] = @Type, [DateCreated] = @DateCreated, [DateLastAccessed] = @DateLastAccessed, [DateLastModified] = @DateLastModified, [Attributes] = @Attributes, [Size] = @size |
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:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO GRANT EXECUTE ON sys.sp_OACreate TO [teste_OA] GRANT EXECUTE ON sys.sp_OADestroy TO [teste_OA] GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [teste_OA] GRANT EXECUTE ON sys.sp_OAGetProperty TO [teste_OA] GRANT EXECUTE ON sys.sp_OAMethod TO [teste_OA] GRANT EXECUTE ON sys.sp_OASetProperty TO [teste_OA] GRANT EXECUTE ON sys.sp_OAStop TO [teste_OA] |
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!”
Perfect !
Thank you, Jorge!
Hug