How to make job so others can call it?

[sschrupp]

I have created a number of jobs that call SSIS pacakges to import data and stuff. In Excel I've set up an interface that lets me run these jobs by calling stored procedures that call the jobs, etc. Works perfect. Yay! But...

When I had another user test this out it fails. Well it turns out that nobody else can see the jobs that I've created. The whole point of this project is for other users to be able to run this. Doh!

Is there some special way that I'm supposed to call the jobs so that any user can call them?

This is what I have in my stored procedure:

EXEC msdb.dbo.sp_start_job N'CapCostFore_Job_ImportAcctWalk'

The step in the job that calls the package is set to run as SSIS_Proxy.

Thanks!

 

[sschrupp]

Oops, forgot to include my Excel side of things.

Public cn As New ADODB.Connection

    ServerName = "blahblahblah\yaddayadda"
    DatabaseName = "PSME"
    
    'Specify the OLE DB provider.
    cn.Provider = "sqloledb"
    
    'Set SQLOLEDB connection properties.
    cn.Properties("Data Source").value = ServerName
    cn.Properties("Initial Catalog").value = DatabaseName
    cn.CommandTimeout = 0
    
    'Windows NT authentication.
    cn.Properties("Integrated Security").value = "SSPI"

    'Open the database.
    cn.Open

    cn.Execute ("CapCostFore.Import_AcctWalkFile")

And the CapCostFore.Import_AcctWalkFile stored procedure calls the job as mentioned.

 

[SQLBill]

You can change the job to be owned by SA and then I think they will be able to run it. Or you can give them access to the MSDB database and grant them the SQLAgentOperatorRole. WARNING: Read the BOL on what this grants them...they can see any job, run any job, change any job, and delete any job.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Another option is to have Excel connect via an application login and make that login the owner of the job.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sschrupp]

Hmm, I can't seem to change the owner of the jobs to anything. When I click on owner it only shows my login and [sa] but when I try to change it to [sa] and exit out it still says it's owned by me when I check. Maybe I don't have permission to do that.

Is it possible for me to change how Excel is connecting to the server so that it signs in as me? Is that what you mean by connect via an application login? How would I go about doing that?