0

When I am only trying to create a table my "DBA" gave reader, writer, "ddladmin" to the database and I am getting this error when trying to create a basic table:

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

Can somebody please let me know the answer.

Marco
  • 3,710
  • 5
  • 23
  • 31
jack
  • 1
  • 1
  • 1
  • Do you have a trigger which will send an e-mail on table creation? – Stijn Wynants Nov 14 '16 at 15:01
  • No i do not have any trigger. It is a simple create script "create table dbo.zzDel (id int )" – jack Nov 14 '16 at 15:04
  • Your DBA will have a trigger in place. Check with him – Stijn Wynants Nov 14 '16 at 15:05
  • Even though i am a DDLAdmin, why would i get that "sp_send_dbmail" error When the DBA has a trigger in place. I am not understanding – jack Nov 14 '16 at 15:08
  • He will have defined a server level trigger. not a database level trigger. information is stored in sys.server_triggers. For example CREATE TRIGGER TriggerName ON ALL SERVER FOR ALTER_DATABASE AS – Stijn Wynants Nov 14 '16 at 15:15
  • Lets assume he has created a server level trigger, but my question is, Can you let me know how i can get rid of that error? What are the permissions required? I know the "DBA" would help me. But i wanted to improve my knowledge on this issue. If you could guide me to any resource that would help me in gathering more information about this particular issue and the permissions required to avoid that issue. Thank you – jack Nov 14 '16 at 15:21
  • This depends on what he has defined in the trigger. For this particular error you will need to have execute rights on the sp_send_dbmail procedure inside the MSDB database. – Stijn Wynants Nov 14 '16 at 15:23
  • The DBA just responded and asked me to try. When i create any table, i am getting this error now "Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid" Can you please shed some light on this? Thank you – jack Nov 14 '16 at 15:28
  • You do not have rights to access the mail profile. Ask him to grant your account permission to the mail profile – Stijn Wynants Nov 14 '16 at 15:35

1 Answers1

2

There are two possibilities here:

  1. Your table create script includes an attempt to send an email
  2. Your DBA has placed a DDL trigger on the database to send an email notification when objects are created.

In the first instance, confirm that there are no attempted email sends from your script.

For the second the DBA might not have set the permissions correctly on the trigger itself, and so it is attempting to send an email using your credentials. Work with the DBA to either grant you permission on msdb..sp_send_dbmail so that emails will flow, or to fix up the trigger.

Nic
  • 4,013
  • 1
  • 15
  • 21
  • Coming to the first possibility, No I do not have any script which tries to send out any email. I am using a very simple create table script "create table dbo.zzDel (id int )"

    Coming to the second possibility, can you please throw some more light into that?

    Thank you

     – jack Nov 14 '16 at 15:03
  • The quickest way would be to ask your DBA if they have a DDL trigger in place on table create, and if they could check that because you are getting this send email error when not trying to send email. – Nic Nov 14 '16 at 15:27
  • He is not a cooperative person :) that is the only reason i am trying to find out the reason :) – jack Nov 14 '16 at 15:30
  • 1
    Unfortunately without having the requisite level of permissions yourself you can't fully diagnose and resolve the problem. The best you can do is bring up the error and ask if there's a DDL trigger in place that might be causing it, and possibly ask for execute permissions on the procedure as a workaround in the interim. – Nic Nov 14 '16 at 15:46