Sql-server – How to run Sql Server stored procedure in Async mode

Please check to see if there is a DENY on the sp_update_job procedure. This may be a direct DENY to your login or it may be a DENY on some group that has your login as a member. DENY always trumps a grant.

If so, then removing your login from the group or having an administrator REVOKE the DENY should solve the problem.

Response prompted by reading Piers7 request. Time has passed, of course.

No, activation stored procedures are called "in a vacuum" so they don't allow passing parameters (there's "nothing" calling it) nor is there even a user context in which to execute, which is why the EXECUTE AS syntax exists and is required for activation.

If you need to abstract the logic, it can be separated into two procedures. The activation procedure is parameterless, but it can call another with parameters. Something like this:

CREATE PROCEDURE [dbo].[CentralizedLogic]
(
    @p1 varchar(MAX) = NULL
)
AS
BEGIN
    ...
END

CREATE PROCEDURE [dbo].[QueueActivator]
AS
BEGIN
    EXEC [dbo].[CentralizedLogic] @p1 = 'blah';
END


CREATE QUEUE [dbo].[MyQueue]
    WITH
        ...
        ACTIVATION
        (
            ...
            PROCEDURE_NAME = [dbo].[QueueActivator]
            ...
        );