Execute permission was denied on the object SP_SEND_DBMAIL

Question

Execute permission was denied on the object SP_SEND_DBMAIL

eyespi20

Say Hey Kid

Points: 711
More actions
February 18, 2013 at 11:04 am

#394210

We moved our production databases from an SQL2008R2 Standard Edition to SQL2012 Enterprise edition this past weekend. I have a DBMailUserRole defined in MSDB that has the execute rights to the sp_send_dbmail stored procedure and the user that is attempting to execute the sp is a member of the role, but I'm still getting this error.
Anyone have any idea what I need to do in order to get this working?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

jvkondapalli SSCommitted Points: 1587 More actions · Answer 1

Firstly, Can you check if dbmail is enable in sys.configurations.

BOL: "Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message."

eyespi20 (2/18/2013)
We moved our production databases from an SQL2008R2 Standard Edition to SQL2012 Enterprise edition this past weekend. I have a DBMailUserRole defined in MSDB that has the execute rights to the sp_send_dbmail stored procedure and the user that is attempting to execute the sp is a member of the role, but I'm still getting this error.
Anyone have any idea what I need to do in order to get this working?
Thanks!

Thanks
Jagan K

eyespi20 Say Hey Kid Points: 711 More actions · Answer 2

Yes, the role does have permission to execute the stored procedure sp_send_DBMAIL and the user is a member of the role. DBMail is enabled and a default profile is defined and test messages go through.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

eyespi20 (2/18/2013)
We moved our production databases from an SQL2008R2 Standard Edition to SQL2012 Enterprise edition this past weekend. I have a DBMailUserRole defined in MSDB that has the execute rights to the sp_send_dbmail stored procedure and the user that is attempting to execute the sp is a member of the role, but I'm still getting this error.
Anyone have any idea what I need to do in order to get this working?
Thanks!

Does the user have a default profile associated ?

Can you post output of below queries and verify if the environment is clustered or not ?

SELECT * FROM msdb.dbo.sysmail_sentitems order by sent_date desc

SELECT * FROM msdb.dbo.sysmail_allitems order by sent_date desc

SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

SELECT * FROM msdb.dbo.sysmail_unsentitems

select * from msdb.dbo.sysmail_configuration

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

jvkondapalli SSCommitted Points: 1587 More actions · Answer 4

Did you also hook up the mail profile in sqlagent's Alert system tab. I don't think this will have any impact on using dbmail through a procedure. But, worth a try

Thanks
Jagan K