The EXECUTE permission was denied on the object sp_send_dbmail

Question

The EXECUTE permission was denied on the object sp_send_dbmail

nichi

Mr or Mrs. 500

Points: 524
More actions
June 16, 2015 at 6:33 am

#305128

Hi,
I have a problem with send mail to SQL Server 2012.
This script:
DECLARE @TESTOBODY nvarchar(300)
SET @testobody = 'Testo'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'My Profile',
@recipients = 'mymail@mymail.it',
@body = @TESTOBODY,
@subject = 'Testo' ;
Run correctly if run from SSMS
retrive error:
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed
if run from job schedule.
I already checked the permissions and are ok
I can not understand the problem.
Tips?
Thanks
nichi

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

The job is running under a different security context compared to the query run directly in SSMS. For T-SQL jobsteps, the owner of the job is the login that runs the step. If the owner of the job is a sysadmin, the job step runs under the SQL Server Agent service account.

The output of the job history will tell you under which security context the step is running.

-- Gianluca Sartori

nichi Mr or Mrs. 500 Points: 524 More actions · Answer 2

Thanks

yes, this is the configuration.

I gave permission to SQL Server Agent service account.

Now the error is

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Does the job history include the "executed as..." Information?

Does the user match the SQL agent service account?

-- Gianluca Sartori

nichi Mr or Mrs. 500 Points: 524 More actions · Answer 4

Yes,

It includes this information,

I did two tests,

with my administrator account and with service account.

Error appears with both account

execute as user: my administrator account

execut user: sql service account

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Ho did you grant permissions on the dbmail procedure?

There's the role "DatabaseMailUserRole" in msdb for that. Is this what you used?

-- Gianluca Sartori

nichi Mr or Mrs. 500 Points: 524 More actions · Answer 6

yes,

are the first checks that I run,

for this reason I do not know where to look for the problem

nichi Mr or Mrs. 500 Points: 524 More actions · Answer 7

Resolved with this command

grant execute on sp_send_dbmail to public

I hope it is the correct way

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

nichi (6/17/2015)
Resolved with this command
grant execute on sp_send_dbmail to public
I hope it is the correct way
Thanks

Nope: this way every user in the msdb database has permissions to run sp_send_dbmail.

GRANT EXECUTE ON object::sp_send_dbmail TO DatabaseMailUserRole looks more appropriate.

-- Gianluca Sartori

nichi Mr or Mrs. 500 Points: 524 More actions · Answer 9

right,

limit permission to just Group

Thanks

end of a bad dream

david.boerwinkle Grasshopper Points: 11 More actions · Answer 10

I also fought this a long time. I found the rest of the answer here:

https://www.sqlservercentral.com/forums/topic/t-sql-problem-using-run-as-user-in-job-step

So, you have to:

use msdb

CREATE USER [Domain\Account] FOR LOGIN [Domain\Account];

exec sp_addrolemember 'DatabaseMailUserRole', 'Domain\Account';

In the T-SQL job step, on the advanced tab, you set the "Run as user" to your Domain\Account.

You also have to set the owner of the job to your Domain\Account.

In this way, you do not have to grant execute to public.

Update: the reason I was trying to run under another account was so that I would not have to grant network permissions to the account under which SQL Agent was running. These permissions would be needed to add attachments to emails. Even with the above steps, it still uses SQL Agent account when trying to access network files.

This reply was modified 4 years ago by david.boerwinkle.