Stored procedures can play an important security role. You can grant users EXECUTE permissions on the stored procedure without granting them direct access to the underlying objects, thus giving you more control over resource access. However, there are exceptions that would require the caller to have direct permissions on underlying objects. To avoid requiring direct permissions from the caller, all following must be true:
The stored procedure and the underlying objects belong to the same schema.
The activity is static (as opposed to using dynamic SQL).
The activity is DML (SELECT, INSERT, UPDATE, or DELETE), or it is an execution of another stored procedure.
If any listed item is not true, the caller will be required to have direct permissions against the underlying objects. Otherwise, the statements in the stored procedure that do not meet the requirements will fail on a security violation.
That’s the behavior in SQL Server 2000, which cannot be changed. That’s also the behavior in SQL Server 2005, only now you can set the security context of the stored procedure to that of another user, as if the other user was running the stored procedure. When you create the stored procedure, you can specify an EXECUTE AS clause with one of the following options:
CALLER (default). Security context of the caller
SELF. Security context of the user creating or altering the stored procedure
OWNER. Security context of the owner of the stored procedure
‘user_name’. Security context of the specified user name
Remember, all chaining rules and requirements not to have direct permissions for underlying objects still apply, but they apply to the effective user, not the calling user (unless CALLER was specified, of course).
In addition, a user that has impersonation rights can issue an independent EXECUTE AS <option> command to impersonate another entity (login or user). If this is done, it’s as if the session changes its security context to that of the impersonated entity.