Post reply

Permissions to execute stored procedures

  • February 10, 2010 at 3:07 am

    #386287

    I need to set up a user to be able to trigger a SQL Server Agent job from within a macro embedded in Excel.

    Currently he's receiving an error that he doesn't have permission to execute the stored proc sp_start_job in the msdb database.

    What server roles and database roles do I need to grant this user to enable him to trigger this proc?

    Thanks,

    Paul

    Keep the rubber side down and the shiny side up.

  • February 10, 2010 at 9:53 am

    #1116967

    I had a similar problem with allowing a job to be started from an asp page. You may be able to adapt this to your situation.

    1. Create a SQL Server login and a user for it in msdb. Grant membership to the SQLAgentUserRole. This role allows the user to start jobs that it owns.

    2. Create a user for the login in master with no permissions. This allows execution of xp_sqlagent_is_starting and xp_sqlagent_notify, which are executed from msdb.dbo.sp_start_job.

    3. Create an unscheduled job, owned by the SQL Server login created above.

    4. Create a stored procedure in a user database that executes sp_start_job as the login that owns the job. The procedure uses EXECUTE AS to switch security context.

    CREATE PROCEDURE [dbo].[Execpkg]

    @JobName varchar (20)

    WITH EXECUTE AS 'jobownerlogin'

    AS

    exec msdb.dbo.sp_start_job @job_name = @JobName

    REVERT

    5. Grant EXECUTE permission on the stored procedure to the user's login.

    Note: this solution requires that cross database ownership chaining be enabled, which may or may not work for you depending on your security standards.

    Greg

  • February 11, 2010 at 4:28 am

    #1117511

    Thank you Greg, I think that will work very well.

    Paul

    Keep the rubber side down and the shiny side up.

  • February 12, 2010 at 6:53 am

    #1117997

    Could you share the Macro that calls the sql stored procedure you wrote in Excel? I think that could come in handy sometime...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy