Service account permissions missing - but what?

[katbear]

Hi,

Here's the scenario. I am running a stored procedure from a 2000 box using a job.

The stored procedure on the 2000 box then calls a stored procedure on a linked 2005 box that uses sp_send_dbmail to send an email. We are using this workaround due to problems sending mail on the 2000 box. It works fine this way.

However, the problem is that when I run the stored procedure on the 2000 box from a JOB, it hasn't been sending the mail.

The sql server agent runs under a windows user. My question is, what permissions are needed for the windows user?? If any.

However, I changed the OWNER of the job to sa, and now it appears to be sending the emails from the job.

So, is this a matter of the sql server agent account or the job owner that was preventing the mail from being sent?

Very confused!

Thanks

 

[ptheriault]

On the 2005 server add the user to the msdb databse and assign it the role of DatabaseMailUserRole.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Yes, that's already been done. We have a special user on the 2005 box, that is just for running the sp_send_dbmail. That part already works.

This is how it works:

SQL SERVER 2000 JOB --> CALLS SP ON 200O BOX --> WHICH THEN CALLS A SP ON A LINKED 2005 BOX WHICH SENDS THE MAIL

ALL PERMISSIONS ON THE 2005 BOX ARE IN PLACE TO SEND MAIL. ALREADY SET UP USER IN THE DATABASE MAIL USER ROLE. THAT PART WORKS.

My question is more:

Does the SQL Server Agent account on the *2000* need any special permissions?

Does the OWNER of the 2000 JOB need any special permissions?

Because once I changed the owner of the 2000 JOB to sa, the emails are being sent. I don't understand why changing the owner of the job made it work.

Thanks

 

[katbear]

I guess more specifically, my question is, does the owner of the job need permissions to run the stored procedure being called by the job?

 

[ptheriault]

As a general rule of thumb 'sa' owns all of the jobs on my SQL Servers. The SQL Agent should already have the correct permissions but the job owner might not. And it sounds like in your case it didn't. Is there a problem with 'sa' owning this job?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

I see that all the other jobs are owned by sa. The person who set up this job didn't use sa. So, using sa is fine.

But I was just wondering more for curiousity's sake, does the owner of the job need execute permissions on the stored procedure called by the job?

I am having a hard time determining this........ due to the fact I've lost track how many emails I've sent myself!

 

[ptheriault]

yes it does.

- Paul

- If at first you don't succeed, find out if the loser gets anything.