Solved

SQL 2005 Database Mail security

Posted on 2007-03-28
3
175 Views
Last Modified: 2011-04-14
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
0
Comment
Question by:paulCardiff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Expert Comment

by:valkyrie_nc
ID: 18808295
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
 

Author Comment

by:paulCardiff
ID: 18808967
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
 
LVL 9

Accepted Solution

by:
valkyrie_nc earned 500 total points
ID: 18809110
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question