Solution
In SQL server 2000 I was trying to import an xls file into a table.
SELECT * into TempData FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Data1.xls', [Sheet1$])
It worked with sa login, but with other users/login it gave error
"Ad hoc access to OLE DB provider [Microsoft.Jet.OLEDB.4.0] has been denied.
You must access this provider through a linked server"
This problem can be solved by
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers \Microsoft.Jet.OLEDB.4.0]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000000
The last line does the trick. If DisallowAdhocAccess is absent, it defaults to 1.
1 comment:
I did everything you said. If i run the OpenRowSet Query under sysadmin role it is working perfectly. But if the same query running under public role it is not working. It is giving above error message. I am trying to use least privilage account for my application. Do we need sysadmin role to run OpenRowSet query? Kindly help me out to resolve the Issue. Thanks in advance
Post a Comment