execute stored procedure RRS feed

  • Question

  • I have setup a user which has execute rights on a stored procedure.  The sp is owned by dbo.  The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to.  Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup?  Thanks!
    Wednesday, March 29, 2006 3:40 PM


All replies

  • a stored procedure is run under the security  context  of who ran the Sps. When the sp is run it is executed in the context of who ever run the Sps. if you deny the user on the base  table. th sps will fail.

    how about a view with an unbroken ownership chain.

    how about using functions instead of Sp.

    just a wild guess....



    Wednesday, March 29, 2006 4:00 PM
  • Alternatives that you can use in SQL Server 2005:

     - sign the procedure and grant permission to access the tables to the certificate used for signing

     - use an EXECUTE AS clause for the procedure to make it execute under a different execution context.

    For signing, I have an example at: http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

    For EXECUTE AS, see documentation at:



    Wednesday, March 29, 2006 10:42 PM