Início / dba / Perguntas / 78603
Accepted
Mansfield
Asked: 2014-10-08 08:00:00 +0800 CST

Permissão de execução negada no objeto sp_start_job

  • 772

Preciso permitir que um usuário abandone um trabalho de agente específico sem ter a capacidade de iniciar outros. Para fazer isso, criei o seguinte procedimento (simplificado):

ALTER PROCEDURE [dbo].[RunJob]
    @job_name nvarchar(200)
WITH EXECUTE AS 'sysadminaccount'
AS
BEGIN
    --SET NOCOUNT ON;
    BEGIN TRY
        EXEC msdb.dbo.sp_start_job @job_name = @job_name 

        -- Wait for job to finish
        DECLARE @job_history_id AS INT = NULL
        DECLARE @job_result AS INT = NULL

        WHILE 1=1
        BEGIN
            SELECT TOP 1 @job_history_id = activity.job_history_id
            FROM msdb.dbo.sysjobs jobs
            INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
            WHERE jobs.name = @job_name
            ORDER BY activity.start_execution_date DESC

            IF @job_history_id IS NULL
            BEGIN
                WAITFOR DELAY '00:00:01'
                CONTINUE
            END
            ELSE
                BREAK
        END

        -- Check exit code
        SET @job_result = (SELECT history.run_status
        FROM msdb.dbo.sysjobhistory history
        WHERE history.instance_id = @job_history_id)

        RETURN @job_result;

    END TRY
    BEGIN CATCH
        THROW;
        RETURN;
    END CATCH
END

No entanto, quando chamo este procedimento (tendo verificado que está sendo executado via "sysadminaccount"), recebo a seguinte mensagem de erro:

Msg 229, nível 14, estado 5, procedimento sp_start_job, linha 1 A permissão EXECUTE foi negada no objeto 'sp_start_job', banco de dados 'msdb', esquema 'dbo'.

A conta é um membro da função sysadmin, então eu entendo que não deve haver problemas para iniciar trabalhos. Verifiquei que é um membro das três funções sqlagent no msdb e todas essas funções têm permissão de execução no sp_start_job.

Como posso conceder a esta conta as permissões apropriadas? Há algo mais que precisa ser feito por causa da personificação?

sql-server sql-server-agent

4 respostas

  1. Best Answer

    Eu não gosto da TRUSTWORTHYopção porque aumenta significativamente sua exposição a uma variedade de coisas. Como Remus explica nesta resposta , ele essencialmente eleva qualquer um db_ownerpara sysadmin. Algumas outras coisas que vale a pena ler são uma série TRUSTWORTHYde Sebastian Meine , o tópico BOL e um artigo da KB (mesmo que as partes do assembly possam não ser relevantes para você neste cenário):

    (E há muitos outros posts por aí alertando contra o uso cego dessa propriedade - só porque funciona e é fácil não significa que seja a coisa certa a fazer - na verdade, isso deve fazer você questionar ainda mais.) Então, eu sugeriria uma abordagem diferente (e ainda existem outras, como assinar com um certificado, mas isso sempre funcionou para mim):

    1. Crie o procedimento em msdb.

    2. Crie um usuário para o login deste usuário em msdb:

      USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

    3. Conceda ao usuário privilégios de execução no procedimento armazenado:

      GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;

    4. Teste-o - chamando o procedimento de outro banco de dados:

      USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

      Ou um teste mais fácil, caso você não queira executar nenhum trabalho agora e não queira esperar até que esse usuário o execute para descobrir se ele tem acesso suficiente a msdb:

      USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

      Resultado deve ser:

      I am really...
---------------
sysadminaccount

    5. Valide que isso não expõe mais nada msdba este usuário:

      USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;

      O resultado deve ser...

      Msg 229, Level 14, State 5, Line 21
      A permissão SELECT foi negada no objeto 'sysjobs', banco de dados 'msdb', esquema 'dbo'.

      ...já que criar um usuário em um banco de dados não dá a ele direitos automáticos para nada nesse banco de dados; você precisa fazer isso explicitamente para esse usuário ou para uma função ou grupo em que ele esteja (incluindo public).

    • 13

  2. Não posso comentar, pois não tenho pontos suficientes para escrever a resposta. Parece que este é um problema de encadeamento de propriedade de banco de dados cruzado. Se esse procedimento for criado fora do msdb, ele não terá acesso aos objetos no banco de dados referenciado, mesmo que "EXECUTE AS" represente o usuário como membro da função sysadmin.

    Então a solução provável é:

    1. Torne seu banco de dados CONFIÁVEL:

      ALTER DATABASE dbname SET TRUSTWORTHY ON;

    2. Habilite o encadeamento de propriedade entre bancos de dados para o banco de dados (pode não ser necessário)

      ALTER DATABASE dbname SET DB_CHAINING ON;
    • 2

  3. Concordo totalmente com que TRUSTWORTHY não é uma abordagem tão confiável. Você deve escolher outra maneira de obter o resultado que você precisa.

    Recentemente, postei uma maneira de conceder o início de um trabalho do SQL Agent a outro usuário ou grupo de usuários. Você pode ver em:

    Permitir que não sysadmin, não proprietário de um trabalho do SQL Server Agent o execute

    A terceira opção, uma tabela de segurança e um procedimento armazenado, oferece mais flexibilidade e menos exposição.

    • 0

  4. No Sql Server:

    basta ir em security->schema->dbo.. Dê um duplo clique em dbo... então clique na aba permission->(blue font)view database permission e sinta-se à vontade para rolar para os campos obrigatórios como "execute"....ajude-se com a escolha .... e concessão, com controles de concessão ou negação .... espero que isso ajude

    • -1

relate perguntas