July 20, 2010 at 8:13 am
When running a stored procedure on SQL Server 2008 Standard 64bit running on Windows 2008 standalone I receive the following error when executed by a non-sysadmin user:
Msg 7415, Level 16, State 1
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server."
Below is the code of the stored procedure, which runs without any issues when executed outside of a stored procedure:
UPDATE OPENROWSET('MSDASQL'
,'MATRIX';
'LOGPRO';
'LOGPRO'
,'SELECT STAT16
FROM blahblahblah.LW616')
SET STAT16 = 'CX'
Can someone provide some suggestions on what may be preventing this from being executed as a stored procedure by a non-sysadmin user? We recently upgraded from SQL Server 2000 and this worked fine beforehand.
July 20, 2010 at 9:51 am
As per books online "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."
Double check your environment for this.
-Roy
July 20, 2010 at 10:44 am
Thank you for the quick response. I have the registry entry for DisallowAdHocAccess set to 0 and the advanced options configured correctly but still getting this error.
July 22, 2010 at 7:48 am
We resolved it by recycling sql server after manually editing the registry.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply