Database Mail--rights needed? 1

[jsteph]

Hi all,
In sql 2005, I've enabled the database mail xp's in msdb. It runs fine from SMS, but out in the wild I have apps that use sql-logins--but those logins have only rights to their database.

Those apps have stored procs that I'm trying to call sp_send_dbmail from which errors due to permissions lack. The only way I can make it work is if I make those db usrs a user in msdb with dbo rights.

My question is--is there a lower auth level I can give those logins to stil run sp_send_dbmail? If not, is this much of a security risk to have sql-auth logins with dbo rights on msdb? In our organization, the users aren't generally tech-savvy, nor are they malicious in nature, but I'm just wondering if that's a best practice or if there's some other way to delegate that authority.
Thanks,
--Jim

 

[SQLBill]

Per the BOL, the only permission needed to execute sp_send_dbmail is the database role DatabaseMailUser in MSDB.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jsteph]

Bill,
Thanks very much! I didn't even see that role becuase it was way up at the top of the list...stupid me...Thanks again,
--Jim

 

[SQLBill]

Don't feel stupid...some roles are easy to overlook, especially when they are only found in one database. I knew about this because I had the same issue and wasn't aware of that role in MSDB.

I would suggest that you make the BOL (Books OnLine) your friend. It comes in handy and most topics do list what permissions are needed.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875