Pass through Queries using Access 97 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Pass through Queries using Access 97

I am getting a permission error when running an EXEC passthrough query from an Access 97 front end. I have tried 2 intances one on my local machine and another on a remote sever. Both have identical ODBS connection links yet the local machine instance works but the remote server instance return an error. The error appears to be something in relation to the xp cmdshell command being run in the bacground and having the correct permissions ie "dbo_owner". I’ve never seen this error before. The remote server can process pass through select queries but none of the action variety. Can anyone help?
Post the error and ensure you have use relevant JET related drivers. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
In a pass-through query that returns an error from SQL Server, it can’t really be a local issue. I wouldn’t bother checking Jet. Note that a member of the db_owner database role does not have sufficient permissions to execute xp_cmdshell. According to Books Online: "By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure." So check the login that you are using to connect to SQL Server, whether it’s a SQL Server login or your Windows login. Have there have been any changes to this login’s effective permissions within SQL Server? Perhaps permission has been denied to one of the other roles to which the login belongs (which overrules a granted permission). In addition to that, the Windows account under which the SQL Server Agent is executing must have sufficient permissions to execute the command that you’re passing on to Windows. Depending on the actual command, this may be a local permission or a permission on a network resource. What type of command is actually being executed?
]]>