Re: Stored proc select denied error

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jeremy Chapman (NoSpam_at_Please.com)
Date: 06/04/04 

Date: Fri, 4 Jun 2004 09:15:48 -0700

Thanks, that fixed it. I allowed cross database ownership chaining on the
two databases in question.

"Gregory A. Larsen" <greg.larsen@netzero.com> wrote in message
news:uoe1nbPSEHA.2976@TK2MSFTNGP10.phx.gbl...
> You problem has to do with cross-database ownership chaining. You need to
> enable cross-database ownership changing. Read this KB article to explain
> more about this problem and how to resolve.
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;810474&Product=sql2k
> --
>
> -------------------------------------------------------------------------- 

--
> --------------------------------------------------------------------------
--
> ----
>
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Jeremy Chapman" <NoSpam@Please.com> wrote in message
> news:ubKScJPSEHA.2704@TK2MSFTNGP10.phx.gbl...
> > I two databases on the same server.  I'm logged in with a user that has
> > access to both databases.  The user has no select/delete/update rights
to
> > any tables.  On the first database the user has exec rights to a stored
> > procedure.  That stored procedure selects * from two tables with a join.
> 1
> > table is on the first database, the other table is on the second
database.
> > When I fire the stored procedure I get a server error 'Server: Msg 229,
> > Level 14, State 5, ...' and the object it is complaning about is
> > database2.dbo.table2
> >
> > The only solution I have found is to enable select access for the user
on
> > database2.dbo.table2 but my intention was to only allow access to tables
> > through stored procedures.  Is there any other way to allow the stored
> > procedure to select from the table on the 2nd database?
> >
> >
>
>

Relevant Pages

  • Re: Using Views for Security
    ... The database in question is our hr/finance database. ... It is on cross-database ownership. ... > Cross-database ownership chaining occurs when a source object depends on ... > login account, SQL Server does not check permissions on the target objects. ...
    (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)
  • 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: EXECUTE Permissions and Cross Database
    ... EXEC sys.sp_configure N'cross db ownership chaining', ... Arnie Rowland wrote: ... stumps me (it works from Database A but not B) ... The stored procedure works fine from database A, but from database B, ...
    (microsoft.public.sqlserver.security)