I have an access.adp that executes a sproc on SQL Server 2000. The sproc
contains a correlated subquery that updates the table ExcelData. Works fine
until someone other than the dbo uses the project. The sproc will not update
a table owned by any non-dbo user. The sproc does not use any owner name so
it should work for any user and update the table they own. However the sproc
will only update the table dbo.ExcelData and that only when it is executed b
y
a dbo - which is right.
Curiously, I copied the SQL statement from the sproc into an Access VBA
module in the project. Changed it into a string and executed it using a
command object. Works perfectly. Will correctly update the table owned y the
user whether the user is the dbo or not.
I'd rather have one sproc on the server than the sql statement in the VBA
module because, actually, the sproc involves about 30 updating queries, not
just one and I'd like to minimize network trafic.
And I do not want a sproc for each potential user even though that would
allow each sproc to identify the table by the owner's name.
Question: How can a sproc be tweaked to recognize the current user when the
user is not the dbo and update the correct table when the sproc is executed
from an ADP?
--
malcolmIt's hard to say from your description what's going on, but I suspect
it's a naming issue rather than a permissions one since you can run
VBA code to achieve the desired result. One way to verify this is to
put a Profiler trace on the app and see what the exact calls are.
Another is to always use the two-part name, either dbo.Tablename or
ownername.Tablename, depending. Unlike Access, SQL Server considers
the name of the object to be the two part owner.object name, so if the
object was created by a sy
min it will be owned by dbo. If it wascreated by a non-sy
min, it will be owned by that person. So you canhave a dbo.Table1, a Fred.Table1, a Cindy.Table1, (etc etc etc) all in
the same database, and each one will need its own sets of permissions
granted to other users. If Access sends a query across the wire for
"Table1", which one would you expect SQLS to use? So in order to avoid
future anguish, have everything owned by dbo, and specify the two-part
name everywhere, in the UI and in your code. And use Profiler to see
what's actually going across the wire. See SQL Books Online, "Using
Ownership Chains" for more information.
--Mary
On Sat, 27 Aug 2005 15:58:02 -0700, "malcolm"
<ramartower@.access312.com> wrote:
>Here is a bug - maybe.
>I have an access.adp that executes a sproc on SQL Server 2000. The sproc
>contains a correlated subquery that updates the table ExcelData. Works fine
>until someone other than the dbo uses the project. The sproc will not updat
e
>a table owned by any non-dbo user. The sproc does not use any owner name so
>it should work for any user and update the table they own. However the spro
c
>will only update the table dbo.ExcelData and that only when it is executed
by
>a dbo - which is right.
>Curiously, I copied the SQL statement from the sproc into an Access VBA
>module in the project. Changed it into a string and executed it using a
>command object. Works perfectly. Will correctly update the table owned y th
e
>user whether the user is the dbo or not.
>I'd rather have one sproc on the server than the sql statement in the VBA
>module because, actually, the sproc involves about 30 updating queries, not
>just one and I'd like to minimize network trafic.
>And I do not want a sproc for each potential user even though that would
>allow each sproc to identify the table by the owner's name.
>Question: How can a sproc be tweaked to recognize the current user when the
>user is not the dbo and update the correct table when the sproc is executed
>from an ADP?|||Hello, Malcolm
>From your description, I understand that:
1. You have some tables with the same name and same structure, owned by
different users (including dbo);
2. You wrote a stored procedure (owned by dbo), that accesses that
table(s), specifying only the name of the table (not the owner)
3. You want the procedure to access each user's table, when invoked by
non-dbo users.
4. Currently, the procedure always accesses the tables owned by dbo
(regardless of which user invoked it).
First, let me say that what you are seeing it's not a bug, it's the
expected behaviour. Normally, when an object is accessed without
specifying the owner name, SQL Server looks first for an object with
that name owned by the current user, and then (if it doesn't exist) it
looks for an object owned by dbo. However, in stored procedures, when
an object is accessed without specifying the owner name, SQL Server
only looks for an object owned by the owner of the stored procedure.
For more informations, see:
http://msdn.microsoft.com/library/e...curity_2sz6.asp
http://msdn.microsoft.com/library/e...des_07_786r.asp
If you want a procedure (owned by dbo) to access each user's table
(when invoked by non-dbo users), then you can use EXEC('...') in the
stored procedure, so the queries are considered in another batch,
outside the scope of the procedure.
For example (considering that you already have user "a" and user "b" in
your database):
CREATE TABLE a.TheTable (a int PRIMARY KEY)
CREATE TABLE b.TheTable (b int PRIMARY KEY)
CREATE TABLE dbo.TheTable (x int PRIMARY KEY)
GO
CREATE PROCEDURE dbo.Test
AS
EXEC ('SELECT * FROM TheTable')
SELECT * FROM TheTable
GO
GRANT EXEC ON dbo.Test TO public
The first statement in the procedure will select from the table owned
by the user that invoked the procedure, but the second statement will
select from the table owned by the user that owns this procedure (dbo).
Razvan
