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?