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]
WITH EXECUTE AS SELF
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.