If we have a stored procedure named dba.foo in database A,
and dba.foo issues DML statements to a table in database B
via a proxy table, is there any way for a user to execute
dba.foo *without* having an individual external login to
database B?

We have such a setup. That is, we have database A
containing stored procedures which access tables in database
B (the remote server) via proxy tables. We have many users
in database A. Currently each user needs a *separate*
individual external login to database B. This is very
cumbersome, since we have a hundred or so users and we add
more frequently due to high turnover. From what I've
researched in the documentation, in this newsgroup, and in
my own testing, it appears there is no way to utilize a
group external login for this purpose. Moreover, it's
unclear to me why it's insufficient for the dba user to have
an external login to database B. I would think that dba.foo
would execute with the permissions of the owner (dba), not
the user calling it.

Is there any workaround for this?

We are using Sybase ASA version 9.

For you to control the user used to make the remote connection
to the remote (B) database, you will need to create extern[al ]login's.
Without an external login, the userid of the connected user
will be used. You can set that up to be DBA our your
application specific, group user, or ....

But if you are also wanting to reduce the total number of
connects to the remote database (B) that will not happen.
Each active use requires it's only connection, since each
active statement ties up the object (singular) that represents
'the' statement statement for that connection.

You can explicitly drop your remote connection, though,
if that helps by issuing an `ALTER SERVER CONNECTION CLOSE ...`
if you so desire; when it is no longer required. [an new
conneciton should automatically start if required but
connecting and disconnecting can be expensive if done
too frequently]

Since you are (effectively) doubling the number of active
connections on your server you may also want to **increase**
the -gn setting on the server to more closely match your usage.
It should have an immediate performance benefit if you
are exhausting your default settings and waisting time
queuing up on that resource.


<Laurie Johnson> wrote