"Ville Tuomola" <vtuomola@gmail.com> wrote in message
news:2f1323a0.0410122258.6462921c@posting.google.com...
> Hi all,
>
> I would like to have stored procedures that select data from another
> database without granting the user running the proc access to the
> other db.
>
> It works fine until I have procs which contain dynamic SQL as it
> breaks the ownership chain. Is there a way to circumvent this
> without granting the user access to the other db (no, I don't want to
> rewrite the dynamic sql to non-dynamic;)?
>
> Regards,
>
> Ville Tuomola

"Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message news:<u6cUIBSsEHA.2804@TK2MSFTNGP14.phx.gbl>...
> An exec statement does NOT run under the same security as the SP does using
> the same ownership chain ( it is a separate batch).
>
> ALthough I haven't tried this, you might try to add an SP on the OTHER
> server which accepts a query string and exec it on the other server... I
> don't if it would work, but it wouldn't take long for you to find out..
>
> You might also try using sp_executsql, which probably would work... Try
> replacing your exec statement with sp_executesql... It is doc'd in books on
> line...
>
> I hope this works for you!....
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Ville Tuomola" <vtuomola@gmail.com> wrote in message
> news:2f1323a0.0410122258.6462921c@posting.google.com...
> > Hi all,
> >
> > I would like to have stored procedures that select data from another
> > database without granting the user running the proc access to the
> > other db.
> >
> > It works fine until I have procs which contain dynamic SQL as it
> > breaks the ownership chain. Is there a way to circumvent this
> > without granting the user access to the other db (no, I don't want to
> > rewrite the dynamic sql to non-dynamic;)?
> >
> > Regards,
> >