Sunday, 3 December 2017

T-SQL Script to Create a Database Mail Account and Profile using Google Account in SQL Server

Creating a Database Mail Account and Profile using Google Account in SQL Server with SQL Script
USE MASTER
 GO
sp_configure 'Show Advanced Options',1;
GO
Reconfigure With Override;
GO 
sp_configure 'Database Mail XPs',1 ;
--GO
--sp_configure 'SQL Mail XPs',0

GO 
Reconfigure
GO

--Creating the DB Mail Profile [Prof_ExcelKingdom]
EXECUTE msdb.dbo.sysmail_add_profile_sp
      @Profile_name = 'Prof_ExcelKingdom',
      @Description  = 'Database Mail Configuration in SQL Server by using Google Account';

--Creating the Account [Acn_ExcelKingdom]
EXECUTE msdb.dbo.sysmail_add_account_sp
    @Account_name            = 'Acn_ExcelKingdom',
    @Email_address           = 'mail***********@gmail.com',
    @Display_name            = 'Excel Kingdom',
    @Replyto_address         = 'mail***********@gmail.com',
    @Description             = 'eMail Account of my Blog',
    @Mailserver_name         = 'smtp.gmail.com',
    @Mailserver_type         = 'SMTP',
    @Port                    = '587',

    @Username                = 'mail***********',
    @Password                = ' ',  --Update later in Account Details using DB Mail Wizard
    @use_default_credentials =  0 ,
    @enable_ssl              =  1 ;

-- Associate Account [Acn_ExcelKingdom] to Profile [Prof_ExcelKingdom]EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = 'Prof_ExcelKingdom',
      @account_name = 'Acn_ExcelKingdom',
      @sequence_number = 1 ; 

Next using below DB Mail Wizard, update Password for the Email Account which is just Created.
Go to SQL Server Management Studio > Management > SQL Server Logs > Configure Database Mail
Next Go to Manage Database Mail Accounts and Profiles
Next Go to View,Change or Delete an existing Account 
Next we will see the below Window where you can update the Password and other details of the Account.


Click Next and Finish to Complete the Email Account Configuration.

Next Test the connection by sending a Test E-Mail



Next enter To Email Address then click Send Test E-Mail, and then say OK

Here I am sending the Email To same account of From.






Next Go to Gmail Account and See the Test mail



----------------------------------------------------------------------------------------------------

The details of a Email Profiles and Email Accounts, You can get from the following System Tables available in MSDB.

USE MSDB
GO
--To check the Sent_Status of last Email.

Select * from sysmail_allitems



--To check the available Mail Profiles
Select * from sysmail_profile

--To Check the available Mail Accounts
Select * from sysmail_account

--To check the available Mail Accounts under a Profile
 Select * from sysmail_profileaccount
     where profile_id=7

--To display Mail Server details
Select * from sysmail_server


--To view DB Mail log details
Select* From Sysmail_log
----------------------------------------------------------------------------------------------------


No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts