Using EXECUTE AS to Create Custom Permission Sets
Specifying an execution context for a module can be very useful in defining custom permission sets. For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table.
By incorporating the TRUNCATE TABLE statement within a module and specifying that module execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.
Consider this stored procedure:
CREATE PROCEDURE TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;
Assume Mary creates this procedure and grants execute permissions on TruncateMyTable to Scott. When Scott executes the stored procedure, the SQL Server 2005 Database Engine verifies the permissions to truncate the table as if Mary were executing the stored procedure. Because she is the table owner, the statement succeeds even though Scott has no direct permissions on the table. Mary has quickly and efficiently extended the permissions to Scott that she wanted to, without giving him more permissions than required to perform the task.
See Also
Concepts
Other Resources
EXECUTE AS Clause (Transact-SQL)
Context Switching