SQL Server Linked Server Permissions

Question

On a SQL Server 7 server, I have a linked server to a Sybase database server running this command:

select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE')

works fine if I'm logged in as an SA.

If I try to run it while logged in as non-SA, it fails with the error:

Server: Msg 7302, Level 16, State 1, Line 1 Could not create an instance of OLE DB provider 'ASAProv.90'.

What permissions do I need to grant the user to let it work as a NON-sa account?

Answer 1

IIRC, try this. I think this is how I fixed it some time ago...

exec sp_configure 'Show Advanced Options', 1; reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1; reconfigure;
go

Edit, attempt 2:

Check this. I've seen this at a previous company (I wasn't sysadmin there) and can't remember what teh DBAs had to do for us to get our linked server to work (also Sybase)

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx