• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

sql server 2008 - WITH EXECUTE AS

Hi experts i am reading about:WITH EXECUTE AS in a create procedure (http://go.microsoft.com/fwlink/?LinkID=209352)

but i do not understand:
CALLER | SELF | OWNER

can explain me with an example, like hands on lab

CREATE PROCEDURE Sales.GetOrders
WITH EXECUTE AS {CALLER | SELF | OWNER | ‘user_name’ }
AS
0
enrique_aeo
Asked:
enrique_aeo
2 Solutions
 
Bob BenderCommented:
Using a link from the page you supplied, there is a page for explaining the options for a Module....

http://msdn.microsoft.com/en-us/library/ms178106(v=sql.105).aspx


You can use the links at the bottom of THAT page, to get more info explaining the options in Transact-SQL.

http://msdn.microsoft.com/en-us/library/ms188354(v=sql.105).aspx

There are samples within those pages.
0
 
lcohanDatabase AnalystCommented:
http://msdn.microsoft.com/en-us/library/ms188354.aspx

CALLER
Specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.
CALLER is the default for all modules except queues, and is the same as SQL Server 2005 behavior.

SELF
EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id column in the sys.sql_modules or sys.service_queues catalog view.

SELF is the default for queues.


OWNER
Specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. OWNER cannot be specified for DDL or logon triggers.


Important
OWNER must map to a singleton account and cannot be a role or group.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now