A developer need to use OPENROWSET function. The steps I did to allow him:
ad hoc distributed queries Server Configuration Option
sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
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
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?