CREATE PROCEDURE [dbo].[stproc_ssb_send]
@iname varchar(20)
WITH EXECUTE AS 'xyz'
AS
BEGIN
DECLARE @DIALOG_HANDLE UNIQUEIDENTIFIER
DECLARE @MSG varchar(max)
SET @MSG='<MESSAGE>
<test>' + @iname + '</test>
</MESSAGE>'
BEGIN DIALOG CONVERSATION @DIALOG_HANDLE
FROM SERVICE START_PROC_PROCESS_SERVICE
TO SERVICE 'EXECUTE_PROC_PROCESS_SERVICE'
ON CONTRACT [PROC_PROCESS_CONTRACT]
WITH ENCRYPTION= OFF;
SEND ON CONVERSATION @DIALOG_HANDLE
MESSAGE TYPE PROC_PROCESS_MT(@MSG);
END CONVERSATION @DIALOG_HANDLE
END
error:
Msg 15517, Level 16, State 1, Procedure [stproc_ssb_send], Line 0
Cannot execute as the database principal because the principal "xyz" does not exist, this type of principal cannot be impersonated, or you do not have permission.
XYZ
is db_owner
/ db_ddladmin
for this database.
Procedure is created using sa
; error occurs while executing.
alter authorization on database::[your_DB_NAME] to [sa];
?