I recently had to setup Database Mail on dozens of SQL Server instances. Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here. My needs were simple so I only needed a single SMTP account and profile. I decided to make the profile the default public one so that all msdb users would use this profile unless a different sp_send_dbmail @profile value was explicitly specified. You might want to extend this script if you need other accounts/profiles, such as separate ones for administrative alerts or user reports. Setup Script Below is the template script I used for my task. The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and you will of course need to customize the mail server name and addresses for your environment. -- Enable Database Mail for this instance E...
~ a resource for the Microsoft SQL Server.