Which Server Roles and User Mappings for Ad-hoc Queries to a Foxpro Tables?

Question

Which Server Roles and User Mappings for Ad-hoc Queries to a Foxpro Tables?

AndrewJacksonZA

SSCarpal Tunnel

Points: 4143
More actions
January 5, 2010 at 1:43 am

#137933

Hi
We're trying to create a new login that only has read permissions, but one of the views contains an ad-hoc query to a Foxpro table, like this:
FROM OpenRowSet('VFPOLEDB.1',
'\\Path\To\Directory\Containing\Foxpro\Tables';;,
'SELECT * FROM FoxProTableName')
The error message that I'm getting is
"Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'VFPOLEDB.1' has been denied. You must access this provider through a linked server."
Which server roles must I assign to allow read only access? I've tried public and setupadmin (setupadmin from this article titled "Understanding SQL Server fixed server roles" by K. Brian Kelly)
I've tried the user mappings of public and db_datareader - I've even tried db_owner - with the two server roles above but they don't work.
It only works when the login is assigned the role of sysadmin. The role of serveradmin doesn't work either.
This is third party code so we can't change it.
Thank you

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

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

FraToLa Ten Centuries Points: 1175 More actions · Answer 1

First create a linked server entry with Provider "Microsoft OLE DB Simple Provider" pointed to the Foxpro table and than use this linked server to open a rowset.

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 2

Thanks Thomas, but we can't change the code. Well, I suppose there is a possibility of doing that since we're working on a copy of the real database and it's just a (relatively) simple SELECT that we're doing...

However, even now that we can (if I can convince my boss) change the query, like I said,

GDI Lord (2010/01/05)
'SELECT * FROM FoxProTableName')

there is no .DBC file for me to connect to (as stated in this list of OLE DB Providers.) I've search the HDDs for a .DBC file and didn't find any.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You won't find a .DBC because you are dealing with FoxPro Free Tables not a FoxPro database (.DBC). The issue you are having is permissions accessing the network location. I've never really dealt with it, but I think you could create a login (DENY CONNECT) the will have sysadmin rights and use EXECUTE AS in the procedure. You probably would have to grant impersonate rights to the users that are calling this procedure.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 4

Thanks Jack. SQL Server is running under the built-in account of Local System and the network share does have the correct permissions (other parts of the program work fine.)

It turns out that I'm not allowed to change the views at all. I'm not even allowed to create another view that has the same code as the other view.