Sql-server – Database Mail works in Production but not in Test Database

database-mailsql-server-2008

I have a Production database that is able to send mail. And a Test that is not able to. They are both on the same SQL Server. The error is (SQL Server 2008):

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I am an intern and have no previous experience with SQL server (just SQL DML, DDL). I tried to debug this by looking at the permissions of the 2 databases but they are the same. So I don't even know how people can send mail from the production.

I looked online and in order for users to be able to send mail they have to be a user under msdb and have the DatabaseMailUserRole. In my case it's a Windows domain. What's funny is that this domain isn't there, so how is production sending mail? I also tried putting the domain there as a user and gave them the permission, but still no luck for the test database.

How do I determine why/how the production database is able to send mail, so that I can debug? Thank you, I would appreciate any help.

Let's say it's too complicated to debug and I want to copy the production database to the test database (as an exact duplicate), will all permissions carry over?

Best Answer

To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database.

To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = '<user or role name>';
GO

Source: Microsoft docs Permission denied on sp_send_dbmail

Related Question