SQL Database, Server Isolated Flat Web Mobile Icon with SQL word. Vector Illustration isolated on modern background
SQL Database, Server Isolated Flat Web Mobile Icon with SQL word. Vector Illustration isolated on modern background

During a visit to a regular customer, a strange request came from the upper IT management. As I was told, they noticed that the global email profile in one of their core SQL servers was mysteriously removed, resulting to errors and delays sending emails to some thousand customers, hence, they wanted me to investigate the issue and advise on how to resolve this.

They even provided me with a relative error message, as shown below:

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112 [Batch Start Line 0]
No global profile is configured. Specify a profile name in the @profile_name parameter.

As there was no clear way to identify what happened through SSMS GUI settings, I decided to investigate msdb database, which is responsible for keeping settings like these.

By opening [msdb].[dbo].[sysmail_principalprofile] with is_default field in it and selecting the top 1000 rows in order to script out the contents of the table, the below result is generated:

This provided the customer a clear view of which user modified the profile.

By Choosing “Configure” From SSMS\Management\Database Mail and selecting “Manage Profile security” afterwards, customer was provided with a list of the available mail profiles where they selected  which one would be the global mail profile.  

Last modified: December 16, 2020

Author

Comments

Write a Reply or Comment