Allow special mailings for all users

Question

I use AD Hoc Distribution Queries to transfer data from MS SQLServer 2008 to MS Access. The process starts using a single SQL statement:

INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\temp\target.mdb;User ID=Admin;Password=;')...testtable select * from dbo.testtable 

To do this, I set the Ad Hoc Distribution Query parameter to 1 through

 sp_configure 'Ad Hoc Distributed Queries', 1 

Everything is working fine while the statement is being executed with the permission of the sysadmin database.

When I try to execute the statement as a regular db user, the statement does not work with a permission denied message.

How can I give all users the ability to perform special mailings?

Thanks in advance

Mark

Answer 1

Taken from EggHeadCafe ; By all accounts, this is the DisallowAdHocAccess registry key; it must exist with a value of 0 before users without the sa role can execute special distributed requests.

In the Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 key, add a DWORD value named DisallowAdHocAccess with a value of 0 .

Allow ad-hoc access to the Jet provider using the registry (it won’t work with Management Studio because it removes the registry instead of setting it to zero, and the missing registry key is interpreted as “not allow ad-hoc access”).

If you previously received an error before trying again, make sure you restart the server or run the DBCC FREEPROCCACHE because it seems that SQL Server will not check the key again if you run the same query twice.

If anyone has a way that is not related to editing the registry, it would be nice to hear it.

If this still does not work, do you try setting up a connected server? MSDN , Access to a Specific Linked Server Tutorial