7

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified):

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

However, when I call this procedure (having verified it is running via "sysadminaccount"), I get the following error message:

Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1 The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The account is a member of the sysadmin role, so I as understand it there shouldn't be any problems kicking off jobs. I've verified that it is a member of the three sqlagent roles in msdb, and those roles all have execute permission on sp_start_job.

How can I give this account the appropriate permissions? Is there something else that needs to be done because of the impersonation?

12

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

  1. Create the procedure in msdb.
  2. Create a user for this user's login in msdb:

    USE msdb;
    GO
    CREATE USER floobarama FROM LOGIN floobarama;
    
  3. Grant the user execute privileges on the stored procedure:

    GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;
    
  4. Test it - either by calling the procedure from another database:

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

    Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to 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;
    

    Result should be:

    I am really...
    ---------------
    sysadminaccount
    
  5. Validate that this doesn't expose anything else in msdb to this user:

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

    Result should be...

    Msg 229, Level 14, State 5, Line 21
    The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

    ...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

1

Can not comment as i have not enough points so writing answer. Seems like this is cross database ownership chaining issue. If this procedure is created outside of msdb then is has no access to objects in the referenced database, even if "EXECUTE AS" impersonates user as a member of sysadmin role.

So probable solution is:

  1. Make your database TRUSWORTHY:

    ALTER DATABASE dbname SET TRUSTWORTHY ON;
    
  2. Enable cross-database ownership chaining for the database (may not be necessary)

    ALTER DATABASE dbname SET DB_CHAINING ON;
    
  • making the database trustworthy did the trick. Thanks a bunch! – Mansfield Oct 7 '14 at 17:27
  • 2
    Why not suggest creating the procedure in msdb? This seems like a lot less exposure than what you've recommended. – Aaron Bertrand Oct 7 '14 at 17:27
  • @AaronBertrand I don't particularly want to give the end user access to msdb. What other exposure am I giving by making the database trustworthy? – Mansfield Oct 7 '14 at 17:41
  • 2
    You can pretty safely give them connect access and the rights on just this procedure. They're not going to be able to do much else without explicit rights. @SebastianMeine wrote a great series on TRUSTWORTHY: sqlity.net/en/1653/… sqlity.net/en/1701/… sqlity.net/en/1710/… - the assemblies parts are not likely relevant but also check this KB support.microsoft.com/kb/2183687 & dba.stackexchange.com/a/25894/1186 – Aaron Bertrand Oct 7 '14 at 17:47
  • @AaronBertrand Aaron, you are right. Implementation is very dependent on wether you grant 'untrusted' users right to create/alter objects in a trustworthy database. In the implementation i worked with so far it is not the case, so your comment is very valuable. – yahor Oct 7 '14 at 17:47
0

I totally agree with that TRUSTWORTHY is not all that trustworthy of an approach. You should choose another way to get the result that you need.

I recently posted a way to grant the starting of a SQL Agent job to another user or group of users. You can see it at:

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

The third option, a security table and a stored procedure, gives you the most flexibility and the least exposure.

0

In Sql Server:

just go to security->schema->dbo.. Double click dbo... then click on permission tab->(blue font)view database permission and feel free to scroll for required fields like "execute"....help yourself with choosing....and grant,with grant or deny controls....hope this will help

  • 3
    This doesn't answer the question. – Colin 't Hart Nov 26 '14 at 9:46

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.