Answered by:

Question

I have a stored procedure that execute several queries to import and export data using openrowset. The procedure works ok on SSMS, but fails when executed by a web page. The difference is the executing user.

On SSMS logged in as Administrator (same as SQL Service account) works ok. On the page we connect with a user with less rights, but enough to select, delete, update, execute procedures, etc.

If the page connects to SQL as Administrator all works ok, but it opens a security problem.

The error getting on the page is:

Err -1
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

As said if the procedure is executed as Administrator the error does not happen. So the question how can I give access to the alternate user to "Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' "?

In addition to the reply above, OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. Instead of run as adminstrator, you can assign ADMINISTER BULK OPERATIONS permission to the account
which connect to SQL Server instance.

<THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
Thanks
MSDN Community Support

Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

In addition to the reply above, OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. Instead of run as adminstrator, you can assign ADMINISTER BULK OPERATIONS permission to the account
which connect to SQL Server instance.

I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings
or concerns you may have.