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.
Comments