Query to View/Enable/Disable Database Mail Profile

1.View Profile
   
   select * from msdb.dbo.sysmail_profile


2. Disable Exisiting Profile


 --EXEC msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'test' ; OR  EXEC msdb.dbo.sysmail_delete_profile_sp @profile_id = [profile_id]
   




3. Enable Profile


sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO


-- Create new profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'test',
@description = 'Profile to send the  emails'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'test',
@principal_name = 'public',
@is_default = 1 ; -- Make this the default profile
GO


--  created the account


EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'test',
@description = 'Medall SMS email account',
@email_address = 'sms@smtp.medallit.in',


@display_name = 'SMS@medall.in',
@replyto_address = 'IT@medall.in',
@mailserver_name = '10.200.2.28',
@username = 'sms@smtp.medallit.in',
@password = 'Pwd',
@port = 25, -- Check with IT  for correct port
@enable_ssl = FALSE -- Enable secured  communication


-- Added account to the profile:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'test',
@account_name = 'test',
@sequence_number = 1
GO


--Make sure the define port is allowed in the server.


--check
EXECUTE msdb.dbo.sp_send_dbmail
 @profile_name = 'test',  
@recipients='test@medall.in',
@subject = 'Test e-mail sent from  mail',
@body = 'This is a test message sent from the newly created account'


GO