Re: Profile Trace: how to tell which database when filtering on user

From: Timo (timo_at_anonymous.com)
Date: 04/12/04 

Date: Mon, 12 Apr 2004 13:40:39 -0400

Sue,
Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
same issue after this:

EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
EXEC sp_dboption 'DB1', 'db chaining', 'ON'
EXEC sp_dboption 'DB2', 'db chaining', 'ON'

Trace shows the procedure being invoked; the IN parameter is there, but all
the OUT parameters are null. When I've encountered similar symptoms in Trace
before, it was because the user lacked execute privileges on the procedure.
But JOE has these privileges by virtue of membership in a db role.

Any other way to troubleshoot this scenario using Trace?
Thanks again.
Timo

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:vcil70hj0j37239l9ds5tevbtfotc6n506@4ax.com...
> If a user is in DB#1 and what you see executed is
> uspGetOrders, it's executing the stored procedure in DB#1.
> It would have to be qualified by the database name to
> execute the stored procedure in DB#2.
> If you are on SQL Server 2000 and if you are on SP3, then
> the issue with the stored procedure could be due to not
> having cross database ownership chaining enabled. If you are
> running under this configuration and have updated your books
> online, you can find more information in books online under
> the topic:
> Cross DB Ownership Chaining
>
> -Sue
>
> On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
> <timo@anonymous.com> wrote:
>
> >Let's say there are two databases, DB#1 and DB#2, and a user JOE. DB#1
and
> >DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
> >the same signature. Is it possible when running a trace that filters on
> >user like JOE, to determine which database the procedure belongs to
which
> >JOE happens to be executing? How can I tell which it is? When I double
> >click on the trace line to view the details, the sp name is displayed
> >without a db or owner prefix. And if I filter on database name, I see
this:
> >exec uspGetOrders @p1 no matter which database I happen to choose. The
> >trace doesn't seem to tell me where the procedure "originates" so to
speak.
> >
> >I'm trying to figure out why the procedure is returning no data when it
is
> >moved to DB#2 and points back to tables in DB1. The procedure worked fine
> >when it was in DB#1. The stored procedure refers to the table as
> >DB1.dbo.Orders and JOE has been granted execute privileges (via a role)
on
> >DB2.dbo.uspGetOrders. JOE had no problem working with the procedure
when
> >it was in the same database as the table. JOE belongs to a role that has
> >select privileges on Orders.
> >
> >The only other thing that has changed is the Initial Catalog when JOE
> >connects. Now it is DB2.
> >
> >Thanks!
> >Timo
> >
> >
> >
> >
> >
> >
>

Relevant Pages

  • Re: current security context is not trusted (cross db ownership ch
    ... ALTER DATABASE only in the specific cases where needed. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... The instance of SQL Server will recognize this setting when the cross ... database option is ignored when 'cross db ownership chaining' of sp_configure is set to 1. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... and database level. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... The reason cross-database chaining works without DB_CHAINING enabled is that you turned on DB_CHAINING at the sever level with the sp_configure 'cross db ownership chaining' option. ... The Best Practice is to leave it off at the server level and set DB_CHAINING at the database level with ALTER DATABASE only in the specific cases where needed. ... EXECUTE ON SCHEMA::MySchema TO MyUsers ...
    (microsoft.public.sqlserver.security)
  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... The server principal "HFDB_2_0_readonly" is not able to access the database "Hotfix_data" under the current security context. ... To add on to Erland's response, the user needs a security context in both databases, even if no permissions are granted. ... I'm trying to set up Ownership Chaining. ... When I set up both a table and view from one logon and granted permissions to the view it didn't work, evidently because "creating user" is not equivalent to OWNER. ...
    (microsoft.public.sqlserver.security)