Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I need some clarification of a situation that we now have when moving to
> > SP3A. If we try and execute a stored procedure from another database it
> runs
> > on the database the stored procedure belongs to.
> >
> > e.g. USE DatabaseA
> > Execute DatabaseB.dbo.upRunsp
> >
> > The sp upRunsp will run on DatabaseB not DatabaseA
> >
> > Is this an effect of cross DB ownership chaining being set off?
> >
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> > David,
> >
> > Did the behaviour change between Gold and SP3A?
> >
> This behavior has been like that since at least 6.5.
> David
>|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> >
> > "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> > news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> > > David,
> > >
> > > Did the behaviour change between Gold and SP3A?
> > >
> >
> > This behavior has been like that since at least 6.5.
> >
> > David
> >
> >
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>