Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

SQL Server - Script to create mail profile and allows stored proc to execute dbmail

I am looking for an automated way to do the following.


I need to create a script that could be ran on any version of sql 2008 and above that will do the following.

1)  create a mail profile.  Lets say we call the profile mycompany

2) Allow this mail profile to be used in a stored proc call that will need to execute send mail

The stored proc will live in the stored procs of the master database.
the call to the send mail of msdb database will come inside of this stored proc.


This stored proc will be called from a sql job.


I get into so many issues from one database to the next from permission ..etc.

I am looking for a solid setup of this to eliminate the many varables I run into.

I also am looking to automate this so it can be setup by other people with less experience on the issues that come up.


Thanks,
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robb Hill

ASKER

Thanks Pawan..I will test this today.
Pawan:

This is great.  I was unable to test completely because the I need to allow the SQL server rights to send outbound mail via SMTP relay.
It will have to be enabled on the exchange box.   Any idea how to add this as well to this script?

I did some googling and seems that might be able to be done in powershell.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
THank you so much!!