Re: Permissions on stored procedures & tables

From: Prakash (Prakash_at_discussions.microsoft.com)
Date: 05/20/05 

Date: Fri, 20 May 2005 02:34:02 -0700

I feel that the procedure you execute first have only static query and the
second query has dynamic query, and the user you are using is supplied with
execute permission alone at this senario if you are trying to execute the
procedre with dynamic sql it will not work and will thro a error as SELECT
permission denied on object 'table name', database 'database name', owner
'owner name'. So, get the Select permission will solve the problem or modify
the sp by avoiding dynamic query.

"Daniel Wilson" wrote:

> 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.
> >
>
>
>