permissions oddity

[evilmousse]

I'm having trouble identifying the exact permissions I need to grant in this situation. We just moved from a single shared testing logon to individual logons for all users. Functions that previously worked are now failing. The two functions failing are:

#1> DoCmd.OutputTo acOutputStoredProcedure, &quot;<spname>&quot;, acFormatXLS, , True
which outputs the result of a sp to excel. This baffles me since this same sp could successfully be bound to a form or report and used by the same user. Further, outputting a table to excel still works. DoCmd.OutputTo acOutputTable, &quot;<tablename>&quot;, acFormatXLS, , True

#2> more important than #1, a few very necessary reports are mail merge word documents I manipulate under the hood to open, pull records from a table, merge into a new document, close the mailmerge and open the flat merged document to the user.

I've tracked the success or failure for these operations down to this: the user must be a member of the server role system administers for them to succeed. It's not really an option for us to leave all users so privileged. The powers that be are not keen on using analysis services to define custom server roles either, which I would have done to narrow the problem down further to which command is the problem, all I know now is that the permission for said command(s) are in sysadmin and no others.

Docmd functions are notoriously opaque to me, so if someone knows where I can find out what's going on to the point of seeing the different actions it takes when outputting a sp versus a table I'd appreciate it. In truth, I could quickly redesign around the excel problem, but the mailmerge problem I have to make work as is. Any and all help is graciously received.

-g

 

[MaffewW]

have you specifically granted the user(s) permission to execute the stored proc in question?

Matt

Brighton, UK

 

[evilmousse]

Yes, and they are known to be able to execute it.

I don't think the problem lies with the sp's
execution, per se, but the docmd and mailmerge
functions are doing something else under the hood
that they're not mentioning to me. Perhaps something
like defining a cursor to iterate through the results.
Mailmerge appears to act that way, anyway. I need
greater visibility into the goings-on of these
functions.

-g

 

[evilmousse]

Found my problem with the help I received in another forum. I was told to try using profiler to pinpoint my error, and it worked well.

Problem was, the tables were being created owned by the users rather than dbo. minor coding changes in all the sprocs and I was good to go.
I needed to change the docmd.outputto acstoredprocedure to be dbo.<procname>, which is odd, because it's not necessary for the similar actable call.

-g