Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute permission was denied on the object SP_SEND_DBMAIL Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:34 AM
Points: 31, Visits: 176
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!
Post #1421304
Posted Tuesday, February 19, 2013 2:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 58, Visits: 544
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
Post #1421483
Posted Friday, February 22, 2013 5:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:34 AM
Points: 31, Visits: 176
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.
Post #1423000
Posted Friday, February 22, 2013 10:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 739, Visits: 3,804
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
Post #1423154
Posted Friday, February 22, 2013 10:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 58, Visits: 544
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
Post #1423156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse