web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Using sp_send_dbmail
Microsoft Dynamics SL (Archived)

Using sp_send_dbmail

(0) ShareShare
ReportReport
Posted on by Community Member

Using Microsoft Dynamics SL 7.0, Microsoft SQL Server 2008.

I am trying to set up a database trigger on the batch table that will send an email when a check batch is released in the system.  I am executing the stored procedure sp_send_dbmail" in the trigger.   It works great when I create and keep a check batch (I am an administrator on the server), but when one of the other users keeps a check batch, they receive the following error:  "The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'".

I have added the users to the "DatabaseMailUserRole" under the msdb database, but the same error still occurs.

Is there another permissions step that I am missing?

Gina

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi

    Execute the below script in SQL Server Management studio

    --Query

    USE [msdb]

    GO

    EXEC sp_addrolemember N'DatabaseMailUserRole', N'XXXXX'

    GO

    Grant EXEC on sp_send_dbmail to PUBLIC

    GO

    --Note XXXXX represents SQL Usesr name

    Thanks

  • Community Member Profile Picture
    Community Member on at

    I already had the user assigned to the DatabaseMailUserRole.  The second script solved my problem.  Thank you very much for the reply!

    Gina

  • Community Member Profile Picture
    Community Member on at

    The statement "Grant EXEC on sp_send_dbmail to PUBLIC"  Just allowed EVERYBODY to send email from your SQL Server with no validation as to what is sent or to whom.

    You should create a stored proc to send the email and use the "Execute as" statement to a SQL User.  Give that SQL user rights in the MSDB to send email.  Allow the normal users to execute the stored proc.  That way you only send the email to the desired people with the desired message.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans