Repost: Profiler shows 'Select * from <stored procedure>'

Hi all,

I am experiencing a weird issue and I hope you can help. We are using a
Microsoft Access 2003 project (.ade) with an SQL Server 2000 database. One
form contains a list box with the 'Table/View/StoredProc' row source type.
The row source itself is a stored procedure.

When an administrator opens this form, he sees data in the list box.
Profiler shows a correct command 'EXEC <stored procedure<parameters>'.
That is allright.

When a non-administrator opens this form, he sees an empty list box.
Profiler shows an incorrect command 'SELECT * FROM "<stored procedure>"'.
The user is a member of the db_owner role.

This is why I now believe that this an Access problem and not an SQL Server
problem. Has anyone ever experienced something like this and how do I solve
it?

TIA,

HJ

Just a guess, but maybe the non-admin user doesn't have Execute permission
for the sproc?

"HJ" <hjiscoolno@spamhotmail.comwrote in message
news:465a8a89$0$322$e4fe514c@news.xs4all.nl...Serversolve

On Mon, 28 May 2007 10:09:04 +0100, "Baz"
<bazz@REMOVEbcap.THEeuro1net.CAPScomwrote:

Or insufficient rights to explore the database design. sysobjects, for
example.

-Tom.

Thank you both for replying. However, the non-administrator user is member
of the db_owner role. That means he should have all rights in the SQL Server
database.

That is why I still believe that it has to do with the client side of this
application in Microsoft Access.

HJ

"Tom van Stiphout" <no.spam.tom7744@cox.netwrote in message
news:fjsl531t52hov5iu8sapivuah6n1s7aj30@4ax.com...permissionOnetype.<parameters>'.procedure>"'.

On Tue, 29 May 2007 10:34:38 +0200, "HJ" <hjiscoolno@spamhotmail.com>
wrote:

Yes, you may be right that it's not a permissions issue. One would
hope for a different error message if it were.
Are you saying that the second user ON THE SAME MACHINE using the same
version of Access and the app, has this bad behavior? That would be a
good test.

-Tom.

Yes, it is the same machine, the same Access .ade file and the
non-administrator user has all rights to use this .ade (otherwise Access
would show a message at start-up saying that it is read-only).

HJ

"Tom van Stiphout" <no.spam.tom7744@cox.netwrote in message
news:voao535e8s2rh63ugqddm717imllb59n61@4ax.com...

On Wed, 30 May 2007 17:32:23 +0200, "HJ" <hjiscoolno@spamhotmail.com>
wrote:

Dang, that's a tough one. I'm running out of ideas. Long shots
include:
* Login as a different non-admin. Still same behavior? If not I would
nuke that user profile.
* Can this user execute sprocs using SQL Server Management Studio?
* Simplify the problem: same behavior with a new database, single
form, single sproc? Same problem with the NorthwindCS sample db?
* This is an XP machine, not Vista? Fully patched?
* Execute the sproc using ADO code rather than as (I'm assuming) the
RecordSource property.

-Tom.