"Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'MSOLAP' has been denied. You must access this provider through a linked server.
"
OPENROWSET can be used to access remote data from OLE DB data sources
only when the "DisallowAdhocAccess" registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.
In BOL, it also states "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option."
So the easiest way to make this "DisallowAdhocAccess" registry option is
Connect to the SQL instance using "Management Studio"
Right click and select the properties for the corresponding provider
under Server Objects -> Linked Servers -> Providers
In this example it is the MSOLAP provider...
Enable the Option "Disallow adhoc access"....
This will create a registry entry DisallowAdHocAccess with value 1 under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\the sql install location\Providers
Open regedit and search for "DisallowAdHocAccess" , change the value from 1 to 0 for the
appropriate provider.....(in this example it is MSOLAP privider...
