Ad hoc access to OLE DB provider

Question

Ad hoc access to OLE DB provider

Pavas Malviya

Ten Centuries

Points: 1109
More actions
September 8, 2005 at 1:22 am

#363578

Hi
I have created a user named :- 'DBUser' a role 'DBRole'
when i try to execute this query i get the follwoing error.
select *
from OpenRowSet (
    'Microsoft.Jet.OLEDB.4.0'
    , 'Excel 8.0; DATABASE=E:\file\RATINGS.xls'
    , 'select * from [Sheet1$]'
    )
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.
But when i use the same query with sa user and as a owner od Database this query goes through.

Kindest Regards,
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Not exactly your situation, but the info may give some pointers where to start looking. http://support.microsoft.com/default.aspx?scid=kb;EN-US;328569

/Kenneth

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 2

i have gone through this link.

but the problem is i don't want to work with registry keys on the

server.One wrong step and the whole system can be down.

Any more help will be appreciated.

I have just found the soulution that if we give a user all the permissions and role this query will work.

But this is not n exact solution.

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

So, are you saying that by making the user sysadmin, the problem is solved?

I would argue that that is not a solution at all! If it would, then we wouldn't need 'security' accounts at all.

I appreciate your caution about the registry keys on a production server. Caution is healthy, but no need to fear. Being frightened is only a sign of not knowing enough, in which case you need to learn and understand, then there is no more fear left.

There is no harm in looking around the registry - you still need to identify what the real problem is. It may not be what that kb talks about after all? And should the kb describe the problem, then a registry change is what is needed, no matter what. Of course, 'wrong steps' can be made anytime and almost anywhere, especially by admin users, that's why we need to be extra careful...

..and test, test, backup and backup again, so that when we do step wrong (which we all do from time to time) we can set things right again as painless as possible.

/Kenneth