Solved

SQL 2005 Database Mail security

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 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