Search code examples
sqlsql-serversp-send-dbmail

Execute permission for SP_SEND_MAIL not working?

I humbly ask for your guidance regarding this matter.

I am trying to execute a stored procedure from SQL SERVER. I used it in a VB.NET application.

When using "SA" login in the connection string, I can execute the code no problem, but if not... I receive this error

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

So, I searched in this site and the others, pointing out to add permissions to the account where the command does not proceed, to DatabaseMailUserRole.

I think I had done that, not using this method though

Here are the screenshots of the things that I did.

enter image description here granted execution

enter image description here

enter image description here

Below is the command that I would like to run in the stored procedure.

                    exec msdb.dbo.sp_send_dbmail 
                    @profile_name = 'SendSQLMail', 
                    @recipients = 'test@gmail.com', 
                    @blind_copy_recipients ='test2@gmail.com',
                    @subject = 'TESTS', 
                    @body =  "A SOMETHING",
                    @execute_query_database = '[FF]',
                    @body_format = 'text'

Please do tell me if you need more details regarding my work

EDIT:

The UserName that I would like to grant access is FF

Solution

  • So, Please tell me if what I did is wrong or what..

    First I read this artice

    GRANT EXECUTE ON object::sp_send_dbmail TO DatabaseMailUserRole

    I got this code and tried to run it, nothing happened, well, the error happened.

    Then the most common solution:

    EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = 'ff';
GO

    Did not work either...

    But when I used

    ALTER DATABASE <mydatabase> SET TRUSTWORTHY ON

    It worked fine... But, does setting the database to being TRUSTWORTHY has its consequences? Please do take note this is just a internal network project.