0
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.

6
  • GRANT CONTROL ON DATABASE::DatabaseName TO XYZ.. try to execute this first and then check if t works?
    – KASQLDBA
    Commented Jan 19, 2016 at 9:31
  • sorry, but no use
    – morex
    Commented Jan 19, 2016 at 11:47
  • 1
    People are editing your question to make it easier to read and to conform to formatting conventions here. Please stop rolling back their efforts. Commented Jan 19, 2016 at 16:16
  • Have you granted authorization as alter authorization on database::[your_DB_NAME] to [sa]; ?
    – Kin Shah
    Commented Jan 19, 2016 at 16:42
  • Is XYZ an AD group? If so it can't be used for this type of thing. (hence the error). If not what type of account is it? Commented Jan 19, 2016 at 19:25

1 Answer 1

-2

ALTER DATABASE SET trustworthy ON

This query solved my prob. Thanks for efforts.

1
  • I added this answer when externally find. I think many of user on Stack network busy in stupid comments instead of helping others
    – morex
    Commented May 10, 2016 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.