10

I'd like to know how to set up permissions within SQL Server to allow my application Login/Role to be able to send email using msdb.dbo.sp_send_dbmail.

I have a database MyDb, a user MyUser who is a member of role AppRole. I have a stored procedure myProc that calls msdb.dbo.sp_send_dbmail. If I execute myProc while logged in as sa it all works fine, but if I execute while logged in as MyUser I get an error:

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

My database does not have TRUSTWORTHY ON, therefore I believe I can't use EXECUTE AS to impersonate a different user, e.g. create myProc with EXECUTE AS OWNER... (MSDN reference)

Therefore I think I need to make my user(s) also users within msdb, but can I do this at the Role level or do I need to make each of my database users also users in msdb?

The database mail profile that I'm using is set to public, so I don't think this is related to the profile permissions.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Rory
  • 40,559
  • 52
  • 175
  • 261
  • 1
    I ended up just creating my users as users within msdb. For simplicity I didn't want to use @Remus Rusanu's suggestion of certificates although in a diff environment that may be a good option. If you have lots of database users it might be possible to use EXECUTE AS to impersonate a single db user and then just give that one user access to msdb - I didn't need to look into that option. – Rory Apr 29 '11 at 16:10

4 Answers4

4

You can use EXECUTE AS and sign your procedure and then use the signature certificate to grant EXECUTE permission in msdb. See Call a procedure in another database from an activated procedure, as well as Signing Procedures with Certificates.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I should have mentioned: the app is deployed to a range of clients, so signing procedures becomes complicated. But otherwise is a good option. – Rory Apr 06 '11 at 21:50
  • I'm not sure I follow why is it an issue. Can't the deployment process (the MSI install or Setup) handle the provisioning of the procedure, signing and granting the required permissions? – Remus Rusanu Apr 06 '11 at 23:06
  • In theory yes, in practice I've found the simpler the better when dealing with a large range of DBAs and environments so I'd prefer not to go down the route of certificates etc. – Rory Apr 29 '11 at 16:06
2

you need give database Role-->databaseusermailuserRole

enter image description here

Vishe
  • 3,383
  • 1
  • 24
  • 23
2

You should be able to just grant MyUser public access to MSDB and then grant execute access on the sp_send_dbmail proc to that user. You can add the user by themselves or create a custom role in MSDB and add all the users that need exec access to sp_send_dbmail.

Quantum Elf
  • 752
  • 4
  • 9
1

According to microsoft you just need to give the users permission to "the DatabaseMailUser database role in the msdb database".

Tony
  • 818
  • 1
  • 7
  • 21