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.

  • GRANT CONTROL ON DATABASE::DatabaseName TO XYZ.. try to execute this first and then check if t works? – KASQLDBA Jan 19 '16 at 9:31
  • sorry, but no use – morex Jan 19 '16 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. – Aaron Bertrand Jan 19 '16 at 16:16
  • Have you granted authorization as alter authorization on database::[your_DB_NAME] to [sa]; ? – Kin Shah Jan 19 '16 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? – Kenneth Fisher Jan 19 '16 at 19:25
-2

ALTER DATABASE SET trustworthy ON

This query solved my prob. Thanks for efforts.

| improve this answer | |
  • I added this answer when externally find. I think many of user on Stack network busy in stupid comments instead of helping others – morex May 10 '16 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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