There are several things that can be done to determine and grant the necessary rights to the objects that are needed. In SQL Server 2005 a new feature has been added "EXECUTE AS" which allows you to impersonate another user in order to validate the necessary permissions that are required to execute the code without having to grant all of the necessary rights to all the underlying objects and commands.
The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a stand alone clause to change the users context.
CREATE PROCEDURE dbo.usp_Test
WITH EXECUTE AS OWNER
There are basically five different types of impersonation
that can be used as part of EXECUTE AS
- the specified user is the person creating or altering the module/SP/Function/Trigger
- this will take on the permissions of the current user
- this will take on the permissions of the owner of the module being called
- a specific user
- a specific login