Solved

SQL 2005 Database Mail security

Posted on 2007-03-28
3
166 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
  • 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now