Bulk operation prerequisites for non-sysadmins

Question

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;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;

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

The sample query he has provided:

SELECT a.*
FROM OpenRowset('MSOLAP','DATASOURCE=bitabular\tabular; Initial Catalog=NPS;',
'SELECT * FROM $SYSTEM.TMSCHEMA_DATA_SOURCES') as a

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?

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

Amirhossein · Accepted Answer · 2022-07-26 12:49:49Z

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

And configuration remote access read this:

1 Answer 1