A developer need to use OPENROWSET function. The steps I did to allow him:

Enable ad hoc distributed queries Server Configuration Option

sp_configure 'show advanced options', 1;  
sp_configure 'Ad Hoc Distributed Queries', 1;  

Then grant Administer Bulk Operations permission to the role, which he is member of.

The sample query he has provided:

FROM OpenRowset('MSOLAP','DATASOURCE=bitabular\tabular; Initial Catalog=NPS;',

Also DisallowAdhocAccess registry option is set to 0 for the specified provider.

But still user get access error:

Ad hoc access to OLE DB provider 'MSOLAP' has been denied. You must access this provider through a linked server

If I understood the remarks in msdn article the authentication delegation is the missing part. But all our logins are windows authenticate mode.

What is the solution?

  • 1
    What is the exact error message the user receives? I don't recall ever having to grant a permission called Administer Bulk Operations to be able to use OPENROWSET().
    – J.D.
    Jul 21, 2022 at 21:14
  • See the link in question. You will understand. Jul 22, 2022 at 11:45
  • The example OPENROWSET() code you've provided in your post does not use the BULK option and therefore doesn't need the Administer Bulk Operations permissions, as far as the information you've provided so far. So again, what is the exact error message the user receives?
    – J.D.
    Jul 22, 2022 at 13:54
  • 2
    @J.D. Error message is now in question. Jul 23, 2022 at 13:05
  • Make a test: Connect with Windows credentials that are machine administrator .Check if code rans. IF rans, it is probably a windows permission problem. If do, try review the permissions of SQL account
    – Rodrigo
    Jul 29, 2022 at 17:13

1 Answer 1


The message is clear, you don't need to do anything on the SQL server.

Check the firewall, access users, permissions on folders, and other security measures for developer users.

Check whether the user login that you are accessing has rights on SQL Server Service or not.

Compare developer user permissions with those of your users.

For add user access read:




And configuration remote access read this:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.