sp_send_dbmail EXECUTE permissions

[NuJoizey]

I've spent some time googling, but I just can't seem to find or derive a straightforward answer to what I believe is a straightforward question.

I have an aspx page. On that page is a dropdown box. If the user selects choice ABC, then it fires off a stored procedure (USP) that calls EXECUTE msdb.dbo.sp_send_dbmail

The error generated is "EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'."

Very simply, I want this USP to fire any time any user in my company chooses choice ABC in the dropdown.

In my SQL installation, there is a user group called STAFF which includes all members of the company. STAFF has login rights to the instance. It is mapped to the msdb database and tagged with the DataBaseMailUser role, but apparently, this isn't enough because the error gets generated when anyone tries to use the interface.

The USP works when logged into MSSQL directly as dbo.

Surely there must be a way to configure MSSQL to send mail from a aspx UI in this fashion - isn't there? What is the proper setup to allow this?

 

[markros]

See

http://www.databasejournal.com/feat.../3626056/Database-Mail-in-SQL-Server-2005.htm

 

[markros]

This is the thread I got this link from

http://forums.asp.net/p/1338459/2703677.aspx#2703677

 

[NuJoizey]

markros - thank you for your reply.

as far as i can tell, mail configuration is setup properly - i have been already using it successfully in jobs that scan the DB nightly and send out emails if records meet a certain criteria. But in that case, sp_send_dbmail isn't being initated by any particular user.

The issue is limited to when a USP that calls sp_send_dbmail is called from an aspx page in my app.

the link you posted doesn't seem to address anything about why users other than the dbo are unable to send mail from a server that is otherwise configured properly. In my case the user is whomever clicks the button in the aspx page.

so my original question remains. In IIS, I am using Windows Authentication, with Anonymous Authentication disabled - if that info is of any use.