Mail Accounts and Profiles Management SQL Server

gistfile1.txt

You can use folowing system stored procedures to get some info about SQL server mail accounts and profiles:
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

The following example creates a Database Mail account and a Database Mail profile. 
The example then adds the account to the profile and grants access to the profile to the DBMailUsers database role in the msdb database.
-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'AdventureWorks2008R2 Administrator',
    @description = 'Mail account for administrative e-mail.',
    @email_address = '[email protected]',
    @replyto_address = '[email protected]',
    @display_name = 'AdventureWorks2008R2 Automated Mailer',
    @mailserver_name = 'smtp.Adventure-Works.com' ;

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AdventureWorks2008R2 Administrator Profile',
    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AdventureWorks2008R2 Administrator Profile',
    @account_name = 'AdventureWorks2008R2 Administrator',
    @sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'AdventureWorks2008R2 Administrator Profile',
    @principal_name = 'ApplicationUser',
    @is_default = 1 ;
    
The following command deletes a Database Mail SMTP account.
SYNTAX:
sysmail_delete_account_sp { [ @account_id = ] account_id | [ @account_name = ] 'account_name' } 
EXAMPLE:
EXECUTE msdb.dbo.sysmail_delete_account_sp  
    @account_name = 'AdventureWorks Administrator' ;

The following command deletes a mail profile used by Database Mail.
SYNTAX:
sysmail_delete_profile_sp  { [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' }
EXAMPLE:
EXECUTE msdb.dbo.sysmail_delete_profile_sp  
    @profile_name = 'AdventureWorks Administrator' ; 
    
The following removes an account from a Database Mail profile.
SYNTAX:
sysmail_delete_profileaccount_sp  {   [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' } ,  
    {   [ @account_id = ] account_id | [ @account_name = ] 'account_name' }
EXAMPLE:
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp  
    @profile_name = 'AdventureWorks Administrator',  
    @account_name = 'Audit Account' ;