Sproc Permissions on Broken Ownership Chain

From: R Thomann (rthomann@wpsic.com)
Date: 07/05/02 

From: "R Thomann" <rthomann@wpsic.com>
Date: Fri, 5 Jul 2002 13:21:15 -0700

SQL7

I am looking to have a stored procedure (x.mysproc) that
selects and updates from various tables (y.theirtbl)
without the user (a) of the stored procedure needing
permissions to the tables (y.theirtbl). So user (a) only
has EXEC permission on sproc x.mysproc. I have tried
changing the owner of the sproc to a, y, and dbo in an
attempt to not break the ownership chain, and all failed.
It is my understanding that sprocs using tables with
different owners than the sproc cause the underlying tables
to need permissions granted to the user of the sproc.

user: a ...

permission: EXEC x.mysproc ...

sproc action: SELECT & UPDATE ...

table: y.theirtbl (owner must stay y as this is table for
a purchased package)

How can I setup owner/permissions on a sproc to do this?

Thanks!

Rodger Thomann

Relevant Pages

  • Re: Sproc Permissions on Broken Ownership Chain
    ... The only way to maintain an unbroken ownership chain here is for the ... execute permissions on the proc. ... > has EXEC permission on sproc x.mysproc. ... y.theirtbl (owner must stay y as this is table for ...
    (microsoft.public.sqlserver.security)
  • execute permissions and scheduled jobs
    ... sp_update_jobschedule sproc from a table trigger. ... that when the records are inserted into the table, which in turn executes the ... I've found that the sp_update_jobschedule sproc has execute permissions ...
    (microsoft.public.sqlserver.security)
  • Re: Permission denied in table yet allowed access in Sproc but still no access
    ... "Graeme Malcolm" wrote in message ... > Is the base table owned by the same user as the sproc? ... > permissions at each break in the ownership chain, ... > I have a table which I am denied access but given the correct permission ...
    (microsoft.public.sqlserver.programming)
  • Re: Permission denied in table yet allowed access in Sproc but still no access
    ... The owner for both obj is dbo ... > different then the permissions on the base table would be applied. ... > permission in a sproc accessing the table, I am still able to access the ... > I have a table which I am denied access but given the correct permission ...
    (microsoft.public.sqlserver.programming)
  • Re: Retrieving SPROC return value using SPT
    ... You cannot use the parenthesis in calling the stored procedure. ... Your parameter must be declared as OUTPUT inside your stored procedure. ... It still not updated after return from> sproc. ... The "@" seems to be sufficient to tell> SQL>> that it's an output parameter. ...
    (microsoft.public.fox.vfp.queries-sql)