Re: Sproc Permissions on Broken Ownership Chain

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 07/06/02 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Fri, 5 Jul 2002 23:45:59 -0500

The only way to maintain an unbroken ownership chain here is for the
referencing procedure to be owned by 'y'. In that case, users need only
execute permissions on the proc. However, direct permissions are
necessary if you use dynamic SQL within procedures.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"R Thomann" <rthomann@wpsic.com> wrote in message
news:1587101c22461$83b3edf0$9be62ecf@tkmsftngxa03...
> 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

  • 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
    ... 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: 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)
  • Sproc Permissions on Broken Ownership Chain
    ... selects and updates from various tables ... without the user of the stored procedure needing ... has EXEC permission on sproc x.mysproc. ... to need permissions granted to the user of the sproc. ...
    (microsoft.public.sqlserver.security)