Stored procedure failed because of "WITH EXECUTE AS SELF" ?

Posted on 2008-11-12
Last Modified: 2012-05-05
I have a storec procedure, doing some kind of cross database queries, create temp tables, and populate data, etc. No problem. But when I added a line "WITH EXECUTE AS SELF", like below:

CREATE PROCEDURE [dbo].[cpTestProcedure]
AS ...

The stored procedure failed (transaction rolled back). Could anyone let me know why it is like this and if it is good to add "WITH EXECUTE AS SELF" in every procedure? (I saw this format in some T-SQL template, so I assume it is a good programming style). Thanks.
Question by:heyday2004
    1 Comment
    LVL 6

    Accepted Solution

    I depends on what context you want the code to execute in. This does a pretty good job of explaining it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now