Re: non-sa users get access denied in OLE DB query

Stu (Stu@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
But turned on, uness you have sysadmin privs, you get this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
access this provider through a linked server.

So, with ad hoc turned on, how do you permission non-sa logins to access
the OLE DB provider?

Books Online gives us this little tidbit:

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.

i search the registry for DisallowAdhocAccess and I found that under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers
there is a whole list of providers. Of these SQLNLCI has DisallowAdhocAccess
set to 0, but the others not. I did a test with a plain-test user, and
it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
did not have DisallowAdhocAccess listed.

So that seems to be the key.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: .NET data provider or OLEDB provider?
    ... >I don't see where you see a built-in query engine. ... >service provider, ... >OLE DB providers don't have to support SQL however, ... > SQL Server linked server, SQL Server can handle the queries. ...
    (microsoft.public.data.oledb)
  • Re: simple question re DELETEing in linked DB
    ... Could not execute query against OLE DB provider 'SQLOLEDB'. ... >>If the remote table is a SQL Server table linked through the OLEDB ...
    (microsoft.public.sqlserver.programming)
  • Re: DMX cmd cant see Access db
    ... You are using SQL Server Management Studio, ... It looks like the error is coming from the Access provider. ... I'm trying to follow the examples from Chapter 5 of "Data Mining With SQL ...
    (microsoft.public.sqlserver.datamining)
  • Re: Oracle Linked Servers
    ... To use distributed transactions with the Oracle OLE DB Provider in your ... I created a blank database in sql server ...
    (microsoft.public.sqlserver.server)
  • adsi linked servers
    ... I'm following the directions for the OLE DB Directory ... I add the linked server with this TSQL ... from OLE DB provider 'ADSDSOObject'". ... If I execute an openquery command from query analyzer I ...
    (microsoft.public.sqlserver.security)