SQL 2008 Database Mail

staggerlee

Hi all,

trying to get to grips with Database Mail. my notes for it so far are below but i still cant find how to allow someone to use the Private profiles without putting them in the sysadmin list.

Notes for Database Mail:


Database Mail

Instance level configuration
Uses SQL Server Agent and MSDB Database

Mail Profiles

Possible to use multiple Mail Profiles. Mail Profiles can hold multiple SMTP Accounts. If a SMTP account fails it will email the next account on list.

Public Profiles / Private Profiles

Only members of the DatabaseMailUserRole in the msdb database can execute sp_send_dbmail.

Public Profiles

Public profiles are available for all members of the DatabaseMailUserRole database role in the msdb database

Private Profiles

Only specified database users, roles, and members of the sysadmin


Anything thing else you think i should put in here? and any explantion to the private profile would be most welcome

Thanks

s

staggerlee

Added


Enable Database Mail from T-SQL

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

RobertKaucher

Have you read this:

Manage Profile Security, Private Tab (Database Mail)

staggerlee

RobertKaucher wrote: »

Have you read this:

Manage Profile Security, Private Tab (Database Mail)

Hi Robert,

Yes my info is mainly copy and paste from pages from there,it seems to jump around the issue but never answer it, i can get only so far

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.

On the Database Mail Configuration Objects page Database Mail Configuration Objects) is goes on to say

For improved security, Database Mail manages access to mail profiles. A profile may be public or private. Public profiles are available for all members of the DatabaseMailUserRole database role in the msdb database. They allow all members of the DatabaseMailUserRole role to send e-mail using the profile. Private profiles are defined for security principals in the msdb database. They allow only specified database users, roles, and members of the sysadmin fixed server role to send e-mail using the profile.



So if im reading this right:

If i want to add a user (I take it, it doesnt matter if they are a SQL Server Account or a Windows Auth account????) to use a Public Profile i just Add them to the DatabaseMailUserRole in MSDB.

But for Private Profile. Unless there in the Sysadmin group i cant find out how to add them.

Or is this just going above whats needed for the exam? Im hoping to take it next week and keep getting caught up in very possibly pointless tasks like this, thinking it could be test worthy?

RobertKaucher

You need to look at the article in its entirity. Notice the links at the bottom to related stored procedures? Here is a direct link:

sysmail_add_principalprofile_sp (Transact-SQL)

I don't even know if this can be done at the GUI....

And yes, I think you are getting into too much detail.

EDIT: If you find a way to do this in the GUI, post it. Because I don't know it and would like to. I need to get better at doing things via SSMS, I tend to do a lot through T-SQL except my DB and table design.