linked server problem

[Carmelo]

I set up a linked server to an Access database.

When I execute a select against the linked server it works fine if I am logged onto sqlserver as 'sa'.

When I log into any other user and run the same select, I get the following message:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.

If I grant the user system admin priveleges, the query now works fine.


I would like to allow a non-sa user the ability to run the query.

Anybody have any ideas.

[Glen]

Majorly you have 2 possibilities:
1. sp_addlinkedserveruser - covered in BOL
2. You can map users on the non 'sa' role to the 'sa' user directly on the security tab of the Enterprice manager linked server.

Good luck

------------
Carmelo at 1/22/01 5:02:37 PM

I set up a linked server to an Access database.

When I execute a select against the linked server it works fine if I am logged onto sqlserver as 'sa'.

When I log into any other user and run the same select, I get the following message:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.

If I grant the user system admin priveleges, the query now works fine.


I would like to allow a non-sa user the ability to run the query.

Anybody have any ideas.