Re: Permissions on stored procedures & tables

From: Daniel Wilson (d.wilson_at_embtrak.com)
Date: 02/24/05 

Date: Thu, 24 Feb 2005 10:28:16 -0500

Thanks, Sue.

Those, especially the last link, explain the problem. This underscores the
recommendation to let DBO own all objects. Since that's not the case in this
DB, I have to give my user explicit permissions on each view & table.

dwilson
"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@4ax.com...
> I'm not sure what the error with OtherOwner is or if you
> ownership chains are intact. Even if they are, with SP3,
> cross db ownership chains were introduced. They are off by
> default for user databases. You can find more information in
> the following article:
> INF: Cross-Database Ownership Chaining Behavior Changes in
> SQL Server 2000 Service Pack 3
> http://support.microsoft.com/?id=810474
> There is also information in the updated version of books
> online under:
> Cross DB Ownership Chaining
> Using Ownership Chains
> or online at:
> http://msdn.microsoft.com/library/en-us/adminsql/ad_config_8d7m.asp
> http://msdn.microsoft.com/library/en-us/adminsql/ad_security_4iyb.asp
>
> -Sue
>
> On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
> <d.wilson@embtrak.com> wrote:
>
> >At one client site, the DB server has 2 databases, that of my application
> >and that for another application. The client's consultant has added a
> >stored procedure which I am to access.
> >
> >Signed onto my application's DB, I run the query:
> >Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
> >
> >I did get an error saying my user wasn't valid on the other DB so I added
it
> >& granted it Execute permissions on MyStoredProc. Now I get error
messages
> >saying:
> >SELECT permission denied on object 'OTHERTABLE', database 'OtherDB',
owner
> >'OtherUser'
> >
> >I thought having the right to execute the stored procedure, I shouldn't
need
> >explicit rights to the tables from which it selects.
> >
> >What do I need to do here? Short of granting myself rights to a bunch of
> >tables in the other DB?
> >
> >Thanks.
>