1

I have create a account and assign server role as public. the assign user mapping on database mydb as dbo, and for msdb also as dbo.

with User mapping setting on msdb, I checked following role:

DatabaseMailUserRole
db_owner
public

then my app login with this account and try to send out email with database mail profile. and I got error as:

System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

If I assign server role sysadmin to this account. then it's working fine.

but I don't want to assign sysadmin to this account. how to resolve this issue?

Weird thing is I also try it on testing server. that account even not in DatabaseMailUserRole on msdb, it's working fine.

The only difference on 2 sql sever box is SMTP authentication setting:
On testing box, is set as "Basic Authentication"
On production box, is set as "Windows Authentication using Database Engine service credentials"

Hi Bridge. Thanks. run EXEC msdb.dbo.sysmail_help_principalprofile_sp and got following on production:

principal_id    principal_name  profile_id  profile_name    is_default
11      guest       1       sqlservice  0
12      mydomainaccount 1       sqlservice  0

On staging, no result.

KentZhou
  • 24,805
  • 41
  • 134
  • 200

1 Answers1

1

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: http://technet.microsoft.com/en-us/library/ms188719%28v=sql.100%29.aspx

Bridge
  • 29,818
  • 9
  • 60
  • 82
  • thanks. I do set DatabaseMailRole for this account on msdb on user mapping. not sure why it still doesn't work. – KentZhou Dec 03 '13 at 16:24
  • Does your user have access to a mail profile? Run this as a sysadmin to see a list of who can access which: `EXEC msdb.dbo.sysmail_help_principalprofile_sp;` – Bridge Dec 03 '13 at 17:13
  • Thanks. run it and got following on production: principal_id principal_name profile_id profile_name is_default 11 guest 1 sqlservice 0 12 mydomainaccount 1 sqlservice 0 On staging, no result. – KentZhou Dec 03 '13 at 17:54