Ownership Chains in SQL Server 2005

Scenario IV

EXECUTE AS SELF is a short cut for the current user (who is creating or altering the routine) to specify himself/herself as the context in which they want the statements of the routine to run as. EXECUTE AS SELF is equivalent to EXECUTE AS USER = user_name, (in the above scenario) where the specified user is the person creating or altering the routine. The catalog stores the actual user ID of the person rather than the value SELF.

1. Login as Mary and create a SP ‘stud_sp_mary’ with “WITH EXECUTE AS SELF” option.

CREATE PROCEDURE stud_sp_mary

WITH EXECUTE AS SELF

AS

SELECT * FROM John.items

2. Now grant execute permission on ‘stud_sp_mary’ to Scott

             GRANT EXECUTE ON stud_sp_mary TO Scott

3. Login as Scott and execute the SP ‘stud_sp_mary’

             EXECUTE Mary.stud_sp_mary

Summary

Use EXECUTE AS CALLER when:

• You want the statements in the routine to execute as the calling user.

• You want to base permission checks for the statements in the routine against the calling user, and rely only on ownership chaining to bypass permission checks on underlying objects.

• Your application does not require hiding underlying referenced objects from the user. Or you only reference objects of the same ownership and can therefore rely on ownership chaining to provide hiding of schema.

Use EXECUTE AS USER = user_name when:

• You want the statements in the routine to execute in the context of a specified user.

• You cannot rely on ownership chaining (for example, the routine accesses objects with different ownership) to hide the underlying schema, and you want to avoid granting permissions on referenced objects.

• You want to create a custom permission set. For example, to provide permissions to DDL operations for which specific permissions cannot usually be granted.

Use EXECUTE AS SELF when:

• You want a shortcut to specifying yourself as the user under whose context you want to run the statements of the routine you are creating or altering to run as.

• You have an application that creates routines for users calling into it, and you want those routines to be created with those users as the execution context. In this scenario, you do not know at design time what the calling user name is.

Amol Kulkarni is an employee of Tata Consultancy Services (TCS), Hyderabad, India.

Published with the explicit written permission of the author. Copyright 2004