0

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?

Improve this question
5
  • 1
    The error is telling you that you dont have appropriate permissions in msdb database. – Kin Shah Sep 27 '18 at 16:02
  • So you mention that there is a user, but without a domain. That sounds to me like a SQL user--not windows user. I would verify that first. Because you may not even have your windows user added at all yet. Then, compare both mail profiles between test and prod. – Jacob H Sep 27 '18 at 16:04
  • How do I compare mail profiles, where are the "directories/files" of interest. Why do you think that one of them can send and not the other? – ConfusedCoder Sep 27 '18 at 16:14
  • is database mail activated on the sql server as a whole or on a per database basis? – ConfusedCoder Sep 27 '18 at 16:16
  • @Kin I said in my post that I added the windows domain to the msdb users and gave them the DatabaseMailUserRole, so doesn't that mean they have the permission? – ConfusedCoder Sep 27 '18 at 16:21
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.