added 5 characters in body; edited title

Source Link

edited Jan 21, 2016 at 8:47

9k
6
45
52

SQL serverServer service broker related rights issue

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.

XYZXYZ is db_ownerdb_owner /db_ddladmin db_ddladmin for this database.

Procedure is created using sa; error occurs while executing.

SQL server service broker related rights issue

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.

SQL Server service broker related rights issue

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.

added 7 characters in body

Source Link

edited Jan 19, 2016 at 16:17

Aaron Bertrand

181.5k
28
402
619

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.

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.

ProcProcedure is created using "sa" & gettingsa; error occurs while exec the procexecuting.

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.

Proc created using "sa" & getting error while exec the proc

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.

added 94 characters in body

Source Link

edited Jan 19, 2016 at 10:46

Remus Rusanu

52k
4
95
172

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=' ' + @iname + ' '

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

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.

Proc created using "sa" & getting error while exec the proc

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=' ' + @iname + ' '

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.

Proc created using "sa" & getting error while exec the proc

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.

Proc created using "sa" & getting error while exec the proc