Friday, March 22, 2019

MSSQL: Configuring SQL Server Private Mail

SQL Server Database Mail enables the database engine to send mail messages and alerting users of the database. The database mail stored procedures are disabled by default and it requires DBA configure and enable before using. Most of every mail configuration resides in the MSDB database.

Private Profile can be accessed by a specific user of the mail host database only. To configure a private, the user must be a part of the msdb database user.


The steps of Mail Configuration consists of Creating Profile, Add Account to the Profile and enabling the configuration.










The User name drop-down box users should already exist in the MSDB database under Security. This is the part that different than Public Profiles.





The parameters are configurable.




The Database Mail will not provide any indications of any configurations.

There are a couple of ways to verify the mail.






OR using SQL Command.

 exec msdb.dbo.sysmail_help_configure_sp;  
 go  
 exec msdb.dbo.sysmail_help_account_sp;  



Use the following query to verify if the mail is enabled or disabled. If the VALUE column is set to 0 then it is disabled and vice versa.

 SELECT * FROM sys.configurations WHERE NAME = 'Database Mail XPs'  

If the Mail is not enabled, it can be enabled with the sp_configure procedure. 

 exec sp_configure 'show advanced options', 1;  
 go   
 reconfigure;  
 go  
 -- 0 is to disable and disabling mail to send messages out.  
 exec sp_configure 'Database Mail XPs', 1;  
 go  
 Reconfigure;  
 go  

The last piece is to use the Mail.


Enable and Configure for usage.


Restart the SQL Server Agent. That's about it.

No comments:

Post a Comment

MSSQL: Getting more detail information for failed jobs.

Often when a job failed, it provides trivial information for diagnostic. In order to expand the detail of the failed job, it can be done th...