How To Limit Email To database Users 2

[Skittle]

How can I limit users from executing email tasks?

If I create a database and add a set of users to it, how can I prevent those users from issuing commands that send emails via msdb.dbo.sp_send_dbmail?



Dazed and confused

 

[mrdenny]

By not setting up a public mail profile and not granting them rights to the private mail profiles.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Skittle]

I am little confused about this.

I thought you could only allocate msdb users to a private email profile. If this is the case, how can a user who is a member of a different database ( i.e. not a member of msdb ) use a profile?





Dazed and confused

 

[thamms]

My understanding is that if a user/group is *not* a member of DatabaseMailRoleUser in msdb, or does not have explicit permissions to execute the sp_send_dbmail proc, or otherwise have sysadmin privs, they can't send mail anyways - regardless if the profile is public or private.

(although private profiles enable you to limit who can send mail)

 

[Skittle]

Yess... I think I agree. The msdb user must be a member of DatabaseMailRoleUser to end email uner any circumstances but if the user is connected to database XYZ instead of msdb and the user does not even have a login mapped to msdb, where is the link enabling the user to send email via a profile?

I'm pretty sure I've done this but the more I think about it the more confused I get about how it actually works.



Dazed and confused

 

[thamms]

It doesn't matter which database a user is connected to. Just forget about that part.

What matters is if the user has permissions to send mail. The way a user gets permission to send mail is:

1.) They are mapped to msdb (either individually or via group login) AND are a member of DatabaseMailUserRole

2.) They have been explicitly GRANTED execute sp_send_dbmail permission (I don't think msdb mapping matters here)

3.) Is a sysadmin

 

[thamms]

Although MrDenny summed it up - you can prevent users from sending mail by using a private profile and NOT granting them use of the private profile.

This will override any msdb permissions anyways.

 

[Skittle]

OK Thanks.

Dazed and confused