July 20, 2012 at 1:26 pm
I have a step in a job that is failing. I'm getting the error
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'
The odd part is that the step in the job calls a job on another server, but doesn't call sp_send_dbmail at any point. This step in the job is only called if another step fails, it then moves on to another step in the job. If the job ends normally or abnormally, an email is properly sent as part of Notification.
I'm not even sure where to start looking for this.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 23, 2012 at 2:24 am
What does the job do? Is it firing any triggers which could be sending mail as part of the job step or the remote job step?
July 23, 2012 at 7:18 am
It runs a stored procedure on the other server that performs an INSERT. That's it. This same stored procedure is called from other steps and runs with no problem.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 23, 2012 at 7:25 am
Run a trace on the server you're getting the error message from. Either Profiler or server-side, doesn't really matter which. Then run the problematic job. You should be able to see what's causing the error in a properly defined trace. You'll want to capture a lot of different events, so if it's a busy server, you'll want to run it server-side so you can parse it out later just for the events related to your connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 7:33 am
GSquared (7/23/2012)
Run a trace on the server you're getting the error message from. Either Profiler or server-side, doesn't really matter which. Then run the problematic job. You should be able to see what's causing the error in a properly defined trace. You'll want to capture a lot of different events, so if it's a busy server, you'll want to run it server-side so you can parse it out later just for the events related to your connection.
Thanks, it isn't "my" server, so I'll have to wait for the person who "owns" it to come back from vacation, but I'll suggest exactly this.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 23, 2012 at 8:08 am
Stefan,
If I'm understanding this correctly, the job exists on server A and the work is done on server B, yes?
If this is true, the linked server login needs read execute permissions on that proc in MSDB on server B. I've had this issue before and that cleared up the error (for me, anyway).
Edited to fix permissions needed.
July 23, 2012 at 8:16 am
Brandie Tarvin (7/23/2012)
Stefan,If I'm understanding this correctly, the job exists on server A and the work is done on server B, yes?
If this is true, the linked server login needs
readexecute permissions on that proc in MSDB on server B. I've had this issue before and that cleared up the error (for me, anyway).Edited to fix permissions needed.
Here's the thing though, if step 1 in the job succeeds, it goes to step 2 which calls this sp on the other server. That works just fine.
If step 1 in the job fails, it goes to step 3 which calls this sp on the other server and I get this error.
The only difference in Steps 2 and 3 is that step 2 sends 'Y' and step 3 sends 'F'.
If all the steps in the job succeed, step 23 is executed and it does the same as step 2. That works just fine.
If a step in the job other than step 1 fails, it goes to step 24 and it does the same as step 3. That works just fine as well.
After step 23 or 24, a notification email is sent to indicate that the job is done. This also works with no problem.
I'm going to check syntax for the 5th time.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 23, 2012 at 8:18 am
Wow. Okay. That is odd.
I honestly don't know what to advise that hasn't already been suggestion. Sorry.
EDIT: Except to ask... Are you positive that the job steps are calling against the same Linked Server?
July 23, 2012 at 8:24 am
Brandie Tarvin (7/23/2012)
Wow. Okay. That is odd.I honestly don't know what to advise that hasn't already been suggestion. Sorry.
EDIT: Except to ask... Are you positive that the job steps are calling against the same Linked Server?
That's part of what I keep checking when checking syntax. That and the open/close quotes.
When I created step 3, I copy/pasted step 2 and changed 'Y' to 'F'
And I just don't see why I'm getting an sp_send_dbmail permissions denied error out of all of this. Other jobs using this account do this same thing with no problems too. Other steps in this job do this same thing with no problems. Trace is likely my only hope.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 23, 2012 at 8:34 am
Stefan Krzywicki (7/23/2012)
Trace is likely my only hope.
"Help me, Obi-Wan Tracenobi. You're my only hope!"
Running away now. @=)
July 23, 2012 at 8:36 am
Brandie Tarvin (7/23/2012)
Stefan Krzywicki (7/23/2012)
Trace is likely my only hope."Help me, Obi-Wan Tracenobi. You're my only hope!"
Running away now. @=)
LOL, before I posted my reply, I deleted the last line. It would have read "Trace is likely my only hope. Other than Obi-Wan"
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 25, 2012 at 7:37 am
Is there an Alert set up on the job? If one or another of the steps fails, it might try to send an alert, and that uses sp_send_dbmail on SQL 2005 and later. Might have nothing to do with your code, your step sequence, etc., if there's an alert set up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2012 at 7:50 am
You know, I just realized we forgot to give you a VR noogie for titling your post "weird error" without any additional details. @=)
Shame! (waggles finger in Stefan's direction) You know better.
July 25, 2012 at 8:00 am
GSquared (7/25/2012)
Is there an Alert set up on the job? If one or another of the steps fails, it might try to send an alert, and that uses sp_send_dbmail on SQL 2005 and later. Might have nothing to do with your code, your step sequence, etc., if there's an alert set up.
Nope, no alert, just the notification on completion.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 25, 2012 at 8:01 am
Brandie Tarvin (7/25/2012)
You know, I just realized we forgot to give you a VR noogie for titling your post "weird error" without any additional details. @=)Shame! (waggles finger in Stefan's direction) You know better.
You're absolutely correct. I was too focussed on "how the hell do I describe this" and should have paid more attention to the title. Of course, the error was so weird that you forgot to scold me until now. : -)
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply