2

I have a job that runs every 2 minutes and sends an email to users after it has gathered job information. Most of the time it is successful but every couple of days it fails with the Execute permission was denied. Right after the failure it runs again just fine.

It is a job that goes through tables and gets information and status of certain jobs. Not SQL Server jobs but proprietary application jobs that has its information stored in tables in the databases. It fashions an email and sends it in HTML format from msdb.dbo.sp_send_dbmail.

It is owned by sa and is not run manually. I'm thinking that since it only happens once every couple of days (sometimes 3 or 4), it could be the content of the message is too big or has some garbage in it that prevents the msdb.dbo.sp_send_dbmail proc from sending.

There is no error handling on the job or step. There is only one step: It executes the proc that aggregates the information and sends the email. The agent log says the same thing that's in the title of the question about permissions.

SQL Server is setup to log failed logins. There have been no failed logins as of a week ago.

0

2 Answers 2

1

Resolved this by setting up a maintenance plan to clean up MSDB. It was very full! Waited to post the answer because I wanted to make sure we went through a few weeks without getting that Execute permission denied error.

0

Community wiki answer:

Modify the job step to record job step output, and configure the stored proc to PRINT some text at the beginning of the proc code. You should be able to tell if the proc is being called by looking at the job history with that in place. Seems like you have some troubleshooting to do.

It's also possible the sp_send_dbmail proc is returning an access denied error as a result of not being able to send the mail to the SMTP server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.