Desabilitar o Agent XPs está impedindo que sp_add_job seja executado com êxito.
Durante a manutenção, nosso software está desabilitando os Agent XPs. Isso precisa ser corrigido, mas TODOS os nossos outros sites continuam sem problemas.
Fizemos alguns testes entre sistemas, o teste consistindo em: desabilitar o Agent XPs, executar EXEC sp_add_job 'hello there';
(Isso cria um trabalho com o nome especificado).
Descobrimos que sp_add_job
falha neste site solitário, mas em todos os outros lugares o trabalho é adicionado muito bem.
Comparamos o código sp_add_job
entre sites comparáveis e o código é idêntico. As permissões também parecem ser as mesmas.
Tentamos depurar o procedimento executando-o como um script, mas curiosamente isso produziu o mesmo resultado quebrado em todos os sistemas (mesmo aqueles que estão funcionando).
Algo está permitindo sp_add_job
funcionar bem em qualquer outro lugar, mas impedindo que funcione em um site.
use msdb
go
declare @jobname sysname
if exists (select * from sysjobs where name = 'test_with_procedure_12345')
EXEC msdb.dbo.sp_delete_job @job_name=N'test_with_procedure_12345', @delete_unused_schedule=1
if exists (select * from sysjobs where name = 'test_without_procedure_12345')
EXEC msdb.dbo.sp_delete_job @job_name=N'test_without_procedure_12345', @delete_unused_schedule=1
exec sp_configure 'agent xps', 0
reconfigure
print ' 1. running with procedure' -- Only one site fails here, the rest all work
exec sp_add_job 'test_with_procedure_12345'
select @jobname = name from sysjobs where name = 'test_with_procedure_12345'
print ' job name (''error'' if unsuccessful): ' + ISNULL(@jobname,'error')
print ' 2. after running with procedure'
exec sp_configure 'agent xps', 1
reconfigure
print ' 3. state reset'
exec sp_configure 'agent xps', 0
reconfigure
print ' 4. running without procedure'
--CREATE PROCEDURE sp_add_job
DECLARE
@job_name sysname = 'test_without_procedure_12345',
@enabled TINYINT = 1, -- 0 = Disabled, 1 = Enabled
@description NVARCHAR(512) = NULL,
@start_step_id INT = 1,
@category_name sysname = NULL,
@category_id INT = NULL, -- A language-independent way to specify which category to use
@owner_login_name sysname = NULL, -- The procedure assigns a default
@notify_level_eventlog INT = 2, -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
@notify_level_email INT = 0, -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
@notify_level_netsend INT = 0, -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
@notify_level_page INT = 0, -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
@notify_email_operator_name sysname = NULL,
@notify_netsend_operator_name sysname = NULL,
@notify_page_operator_name sysname = NULL,
@delete_level INT = 0, -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
@job_id UNIQUEIDENTIFIER = NULL ,--OUTPUT,
@originating_server sysname = NULL -- For SQLAgent use only
--AS
BEGIN
DECLARE @retval INT
DECLARE @notify_email_operator_id INT
DECLARE @notify_netsend_operator_id INT
DECLARE @notify_page_operator_id INT
DECLARE @owner_sid VARBINARY(85)
DECLARE @originating_server_id INT
SET NOCOUNT ON
-- Remove any leading/trailing spaces from parameters (except @owner_login_name)
SELECT @originating_server = UPPER(LTRIM(RTRIM(@originating_server)))
SELECT @job_name = LTRIM(RTRIM(@job_name))
SELECT @description = LTRIM(RTRIM(@description))
SELECT @category_name = LTRIM(RTRIM(@category_name))
SELECT @notify_email_operator_name = LTRIM(RTRIM(@notify_email_operator_name))
SELECT @notify_netsend_operator_name = LTRIM(RTRIM(@notify_netsend_operator_name))
SELECT @notify_page_operator_name = LTRIM(RTRIM(@notify_page_operator_name))
SELECT @originating_server_id = NULL
-- Turn [nullable] empty string parameters into NULLs
IF (@originating_server = N'') SELECT @originating_server = NULL
IF (@description = N'') SELECT @description = NULL
IF (@category_name = N'') SELECT @category_name = NULL
IF (@notify_email_operator_name = N'') SELECT @notify_email_operator_name = NULL
IF (@notify_netsend_operator_name = N'') SELECT @notify_netsend_operator_name = NULL
IF (@notify_page_operator_name = N'') SELECT @notify_page_operator_name = NULL
IF (@originating_server IS NULL) OR (@originating_server = '(LOCAL)')
SELECT @originating_server= UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
--only members of sysadmins role can set the owner
IF (@owner_login_name IS NOT NULL AND ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME())
BEGIN
RAISERROR(14515, -1, -1)
--RETURN(1) -- Failure
--NOTE: replaced with select to run outside of original sp_add_job procedure
SELECT 1
END
-- Default the owner (if not supplied or if a non-sa is [illegally] trying to create a job for another user)
-- allow special account only when caller is sysadmin
IF (@owner_login_name = N'$(SQLAgentAccount)') AND
(ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
BEGIN
SELECT @owner_sid = 0xFFFFFFFF
END
ELSE
IF (@owner_login_name IS NULL) OR ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME()))
BEGIN
SELECT @owner_sid = SUSER_SID()
END
ELSE
BEGIN --force case insensitive comparation for NT users
SELECT @owner_sid = SUSER_SID(@owner_login_name, 0) -- If @owner_login_name is invalid then SUSER_SID() will return NULL
END
-- Default the description (if not supplied)
IF (@description IS NULL)
SELECT @description = FORMATMESSAGE(14571)
-- If a category ID is provided this overrides any supplied category name
EXECUTE @retval = sp_verify_category_identifiers '@category_name',
'@category_id',
@category_name OUTPUT,
@category_id OUTPUT
IF (@retval <> 0)
--RETURN(1) -- Failure
--NOTE: replaced with select to run outside of original sp_add_job procedure
SELECT 1
-- Check parameters
EXECUTE @retval = sp_verify_job NULL, -- The job id is null since this is a new job
@job_name,
@enabled,
@start_step_id,
@category_name,
@owner_sid OUTPUT,
@notify_level_eventlog,
@notify_level_email OUTPUT,
@notify_level_netsend OUTPUT,
@notify_level_page OUTPUT,
@notify_email_operator_name,
@notify_netsend_operator_name,
@notify_page_operator_name,
@delete_level,
@category_id OUTPUT,
@notify_email_operator_id OUTPUT,
@notify_netsend_operator_id OUTPUT,
@notify_page_operator_id OUTPUT,
@originating_server OUTPUT
IF (@retval <> 0)
--RETURN(1) -- Failure
--NOTE: replaced with select to run outside of original sp_add_job procedure
SELECT 1
SELECT @originating_server_id = originating_server_id
FROM msdb.dbo.sysoriginatingservers_view
WHERE (originating_server = @originating_server)
IF (@originating_server_id IS NULL)
BEGIN
RAISERROR(14370, -1, -1)
--RETURN(1) -- Failure
--NOTE: replaced with select to run outside of original sp_add_job procedure
SELECT 1
END
IF (@job_id IS NULL)
BEGIN
-- Assign the GUID
SELECT @job_id = NEWID()
END
ELSE
BEGIN
-- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)
IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
BEGIN
RAISERROR(14274, -1, -1)
--RETURN(1) -- Failure
--NOTE: replaced with select to run outside of original sp_add_job procedure
SELECT 1
END
END
INSERT INTO msdb.dbo.sysjobs
(job_id,
originating_server_id,
name,
enabled,
description,
start_step_id,
category_id,
owner_sid,
notify_level_eventlog,
notify_level_email,
notify_level_netsend,
notify_level_page,
notify_email_operator_id,
notify_netsend_operator_id,
notify_page_operator_id,
delete_level,
date_created,
date_modified,
version_number)
VALUES (@job_id,
@originating_server_id,
@job_name,
@enabled,
@description,
@start_step_id,
@category_id,
@owner_sid,
@notify_level_eventlog,
@notify_level_email,
@notify_level_netsend,
@notify_level_page,
@notify_email_operator_id,
@notify_netsend_operator_id,
@notify_page_operator_id,
@delete_level,
GETDATE(),
GETDATE(),
1) -- Version number 1
SELECT @retval = @@error
-- NOTE: We don't notify SQLServerAgent to update it's cache (we'll do this in sp_add_jobserver)
--RETURN(@retval) -- 0 means success
--NOTE: replaced with select to run outside of original sp_add_job procedure
SELECT @retval as 'errorcode result'
END
print ' 5. after running without procedure'
set @jobname = null
select @jobname = name from sysjobs where name = 'test_without_procedure_12345'
print ' job name (''error'' if unsuccessful): ' + ISNULL(@jobname,'error')
exec sp_configure 'agent xps', 1
reconfigure
Se você executar o código anexado, o erro da etapa 4:
Msg 15281, Level 16, State 1, Procedure sp_verify_job, Line 2
SQL Server bloqueou o acesso ao procedimento 'dbo.sp_verify_job' do componente 'Agent XPs' porque este componente está desativado como parte da configuração de segurança para este servidor. Um administrador de sistema pode habilitar o uso de 'Agent XPs' usando sp_configure. Para obter mais informações sobre como habilitar 'Agent XPs', consulte "Configuração da área de superfície" nos Manuais Online do SQL Server.Msg 515, Level 16, State 2, Line 178
Não é possível inserir o valor NULL na coluna 'category_id', tabela 'msdb.dbo.sysjobs'; coluna não permite nulos. INSERIR falha.
É o mesmo erro que estamos recebendo na etapa #2.
Por fim, tentamos descartar e recriar o
msdb
banco de dados no servidor em questão e o problema desapareceu.Por algum motivo, a pergunta parece estar faltando a mensagem de erro exata, que é uma informação crucial para a depuração. Se isso pudesse ser adicionado à pergunta, seria muito útil. Mesmo que essa mensagem nunca tenha sido salva em algum lugar e não seja mais possível obtê-la desde
msdb
que foi recriada, até mesmo uma paráfrase da mensagem de erro ajudaria.É ambíguo se "mesmo resultado quebrado" se refere ao erro recebido ao executar o
sp_add_job
código como um script em todos os servidores, mas ainda distinto do erro principal relatado aqui ao executar o procedimento armazenado em uma instância, OU se for o mesmo erro entre todas as instâncias que executam o código proc como um script e executam o proc nessa instância.No mínimo, posso explicar por que o mesmo código usado no
sp_add_job
procedimento armazenado não funciona quando executado fora do procedimento armazenado: ele está obtendo permissões adicionais por ser assinado pelo##MS_AgentSigningCertificate##
Certificado. Você pode ver a lista de objetos que são assinados e por que eles são assinados, usando a seguinte consulta:Supondo que você esteja recebendo o mesmo erro (ou semelhante) executando o procedimento armazenado nessa instância, é possível que alguém tenha alterado
sp_add_job
(pelo menos executadoALTER PROCEDURE
mesmo que o código permanecesse o mesmo) ou executadosp_refreshsqlmodule
nele, o que descarta a assinatura e, portanto, descarta essas permissões extras associadas a esse Certificado.É claro que, como
msdb
já foi recriado, pode não ser possível investigar mais. MAS, nesse caso, SE você tiver um backup dele e puder restaurá-lo, execute a mesma consulta mostrada acima e veja sesp_add_job
aparece. Presumo que não, e isso só pode ser devido às duas razões que mencionei acima, ou alguém explicitamente descartando a assinatura (mas isso parece bastante improvável, eu acho).Agora que a mensagem de erro foi adicionada à pergunta, ela parece estar relacionada às permissões. Se você tiver uma instância com a qual possa testar (
msdb
pode ser restaurada / recriada facilmente), tente reproduzir o erro executando primeirosp_add_job
para garantir que funcione e execute:e tente
sp_add_job
novamente para ver se você recebe o mesmo erro "Agent XPs".Também pode ser que tenha
sp_verify_job
sido alterado, e nãosp_add_job
, já que "verify" é o mencionado na mensagem de erro, então tente esse também.