SQL 2005 Database Mail security

I have two questions here regarding SQL 2005 Database Mail

1) Is there a way to use Database Mail for multiple applications - if so how does it work
2) Is there a way in which i can create a user account whos only purpsoe is to use db mail and that it i.e. not have access to any other sensitive information - if so how would i do this

I know you can create Database mail accounts and profiles but if i honest i'm a bit confused how these work and what there purpose are.

Any guidence would be much appreciated as this is very important for my company

Thanks in advance
Paul
paulCardiffAsked:
Who is Participating?
 
valkyrie_ncConnect With a Mentor Commented:
1.  This link http://msdn2.microsoft.com/en-us/library/ms189879.aspx has a bit more info about public vs. private profiles, but as I understand it, a private profile can only be accessed by designated members of the MSDB database, whereas a public profile can be access by all members of the MSDB database.  SQL 2005 will use a default private profile before a default public profile, so I'd try setting it to private first without granting access & see if that works or not.  :)

2.  AppA_MailUser should have access to the msdb database,
3.  and should be a member of the DatabaseMailUserRole

(See here:  http://msdn2.microsoft.com/en-us/library/ms189879.aspx  and here: http://msdn2.microsoft.com/en-us/library/ms188719.aspx for more detailed information.)

4.  I'm not sure I understand what you mean.  :)
5.  I guess we'll see after the first test! :)

hth

valkryie_nc
0
 
valkyrie_ncCommented:
You can use dbmail from any application.  Just run the sp_send_dbmail procedure in the msdb database.  See this link for all the parameters & such:  http://msdn2.microsoft.com/en-us/library/ms190307.aspx  If you're dealing with .Net, just put the stored procedure call in a SqlCommand to run it.

You'll probably want to create an email address specifically for the SQL Server's Mail Profile to use.  The profile allows you to control the 'from' as well as the server it's using, to keep you from having to enter that data in your application.  You can also create a user that only has access to the msdb database and only has permissions to execute that specific stored procedure; you'll just need to make sure that you use the correct connection when you execute the sp.  I  *think* you need to use the EXECUTE AS statement in SQL when you're creating permissions for that user's access.  The following pages might give more insight into this statement:

http://msdn2.microsoft.com/en-us/library/ms190307.aspx
http://msdn2.microsoft.com/en-us/library/ms190384.aspx

hth

valkyrie_nc
0
 
paulCardiffAuthor Commented:
Hi valkyrie_nc
Thanks for you feedback - however i'm curious i.e. I've created a Profile call appA_Profile and consequently created a users call AppA_MailUser - now all i want this user to be able to do is be able to send mail via AppA_Profile. and to do this i willing to create another connection string thus i think i'm right in saying making "Exectue as" redundant.

As such i curious how i should set the following i.e.
1) Do i make the profile public or private?
2) What do i set as the default db for AppA_MailUser
3) 2) What do i set as the server roles for AppA_MailUser
4) And how do i set it so AppA_MailUser can access AppA_MailUser
5) Is there any thing else i need to configure?

Thanks
Paul
0
All Courses

From novice to tech pro — start learning today.