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?

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