execute stored procedure

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!

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....

|||

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:

http://msdn2.microsoft.com/en-us/library/ms187926.aspx

Thanks
Laurentiu