none
Permission Problems executing SSIS job - PLEASE HELP!

    Question

  •  

    After researching best practices I decided to execute run my SSIS package via a job using sp_start_job within a stored procedure. 

     

    The following error is generated when I run the stored procedure and I want to resolve this by granting the least amount of permissions to make things more work. 

     

    The actual error when I run the stored procedure containing the msdb.dbo.sp_start_job command is:

     

    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'.

     

    My application uses a SQL server authentication login to interface to a SQL 2005 database. This SQL login has EXECUTE permission on all the user stored procedures in my database including this new one which contains the sp_start_job command, it cannot access tables directly.  This strategy has been working fine until trying to execute my package. This package imports a flat file into a database table.  The package has been loaded into SQL Server - i.e. not executed from the file system.

     

    I created a credential using a domain account which has sysadmin access. I next created a proxy using this credential for SSIS package execution.  Next I created a job owned by the SQL Server login with a step of type SQL Server Integration Services Package which runs my package with "Run As" the proxy create earlier. 

     

    I know I have to assign some privledges in msdb for the proxy and or job owner but I have tried many things already with no luck. It is important to keep things as "locked down" as possible.  

     

    Any help appreciated.

     

     

     

     

     

     

     

     

     

     

     

    Tuesday, June 24, 2008 5:17 PM

Answers

  • Argh, everything I just wrote got wiped out.  =((

     

    I'm feeling too lazy to retype it all, but try this:

     

    Code Snippet

    USE msdb

    GO

    sp_grantdbaccess 'YourSQLLoginHere'

    GO

    GRANT EXECUTE ON sp_start_job TO YourSQLLoginHere

    GO

     

     

     

    Tuesday, June 24, 2008 10:15 PM

All replies

  • What have you tried in MSDB with no luck?  The error message is telling you that the account you're using to execute the job does not have execute permission on the stored procedure.  At a minimum, it will need access to MSDB and execute permission on the job stored procedures.

     

    Tuesday, June 24, 2008 5:54 PM
  • In MSBD I have added both the SQL login user and the proxy domain account to SQLAgentOperator, SQLReaderOperator and SQLUserOperator and TargetServers roles yert I still get the error.

     

    Why is this so difficult......

     

     

    Tuesday, June 24, 2008 6:12 PM
  • Because the powers that be like to see us banging our heads on our desks??

     

    I'd just try granting the accounts access to MSDB and then execute permission to sp_start_job, and see what happens.  Of course, there may be other sprocs that are required to complete the job.

     

    Another option is to have the SQL Server account (if you're using a domain account) run the job via the SQL Agent.  This is the easiest alternative.

     

    Tuesday, June 24, 2008 7:17 PM
  •  

    <I'd just try granting the accounts access to MSDB and then execute permission to sp_start_job.

    I have added both the SQL Server login and the Domain account used for the proxy to the 3 SQLAgent roles in msdb but I still have the problem. 

     

    <Another option is to have the SQL Server account (if you're using a domain account) run the job via the SQL Agent.

    My job is not scheduled because it is initiated by stored procedure which is invoked on demand from a .NET application. The .NET application accesses the database via a SQL server login which has access to run stored procedures only. This is secure and works very well. I need to maintain the minimal priviledges that the SQL Server login.

     

    Has anyone been successful with this strategy?

     

    1) Stored procedure invoked by a SQL Server login having execute access user SP's only.

    2) The stored procedure includes a call to sp_start_job.

    3) The job is owned by the SQL Server login and contains a single step for invoking a SSIS package loaded on the server. (I have tried making the domain account owner of the job also with no success)

    4) The job uses a proxy for SSIS package step with a credentials from a domain account which belongs to sysadmin.

    5) Both the domain account and SQL login have been added to msdb SQLAgentUser, SQLReaderUser, SQLOperatorUser, TargetServers, db_owner, public database roles.

     

    When the job executes via SQL Server login, the following error is generated:

     

    The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

     

    Thanks in advance.

     

     

    Tuesday, June 24, 2008 8:24 PM
  •  HarpMaster wrote:

     

    I have added both the SQL Server login and the Domain account used for the proxy to the 3 SQLAgent roles in msdb but I still have the problem. 

     

    This doesn't say anything about those roles having the appropriate permission to execute anything.

     

     HarpMater wrote:

    The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

     

     

    Run this while connected to MSDB.

     

    Code Snippet

    SELECT d.Name AS 'User/Role', CONVERT(VARCHAR(40), B.NAME) AS Object, A.NAME AS Permission

    FROM MASTER..SPT_VALUES A, SYSOBJECTS B, SYSPROTECTS C, SYSUSERS D

    WHERE C.ACTION = A.NUMBER AND C.ID = B.ID AND C.UID = D.UID AND D.NAME IN ('SQLAgentUserRole', 'SQLAgentOperatorRole', 'SQLAgentReaderRole')

    AND A.TYPE = 'T' AND B.TYPE IN ('U', 'V', 'P')

    ORDER BY B.TYPE, B.NAME

     

     

    What I was suggesting is simply to grant DB Access to your SQL Login and explicitly grant execute permission to the stored procedures.
    Tuesday, June 24, 2008 8:57 PM
  • Hi Chris,

     

    I ran the your query while connected to MSDB. Many rows were returned, a single entry for sp_start_job:

     

    User/Role              Object            Permission

    ================       ========          ============                 SQLAgentUserRole          sp_start_job        Execute

     

     

    Unless I'm mistaken this tells me that users in the SQLAgentUserRole can execute

    sp_start_job. Both the SQL Login and Domain login are in this role inside msdb. 

     

     

    I am missing something fundamental here and it is driving me crazy :-)

     

     

    <What I was suggesting is simply to grant DB Access to your SQL Login and explicitly grant execute permission to the stored procedures.

     

    I right-clicked on the msdb node in SSMS, selected 'properties' then permissions and selected grant with grant options for the Execute permission. Is this what you are suggesting I need to do?

     

     

    Thanks

     

    Tuesday, June 24, 2008 9:23 PM
  • Argh, everything I just wrote got wiped out.  =((

     

    I'm feeling too lazy to retype it all, but try this:

     

    Code Snippet

    USE msdb

    GO

    sp_grantdbaccess 'YourSQLLoginHere'

    GO

    GRANT EXECUTE ON sp_start_job TO YourSQLLoginHere

    GO

     

     

     

    Tuesday, June 24, 2008 10:15 PM
  • Hi Chris,

     

    Thanks so much for helping me out, you are a genius!

     

    That worked.

     

    I had other permission problems after this and needed access to xp_sqlagent and sysjobs. The  script I ended up with is:

     

    Code Snippet

    USE msdb

    GO

    sp_grantdbaccess 'MySQLLogin'

    GRANT EXECUTE ON sp_start_job TO MySQLLogin

    GRANT SELECT on sysjobs TO MySQLLogin

    GO

    USE master

    GO

    sp_grantdbaccess 'MySQLLogin'

    Grant Execute on xp_sqlagent_enum_jobs to MySQLLogin

    GO

     

     

    Briefly, can you please tell me what vunerabilities have I opened by doing the above.

     

    Thanks again!

     

    Warren

    Thursday, June 26, 2008 12:57 AM
  • Hi Warren,

     

    I'm glad I was able to help you out with this.  Unfortunately, I'm not a network security guru, but I think it comes down to how secure your network is, in general.  I don't believe that you've opened up your system to any prying eyes that are looking for access to your data, though.

    Thursday, June 26, 2008 6:54 PM