sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Notifications',
@description = 'Profile used for sending outgoing notifications using Gmail.' ;
GO
-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Notifications',
@principal_name = 'public',
@is_default = 1 ;
GO
-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'AccoungName',
@description = 'Mail account for sending outgoing notifications.',
@email_address = 'AccoungName',
@display_name = 'AccoungName',
@mailserver_name = 'smtp.mailserver',
@port = 587,
@enable_ssl = 1,
@username = 'AccoungName',
@password = 'AccoungPassword' ;
GO
--change port
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'AccoungName',
@port = 587;
GO
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Notifications',
@account_name = 'AccoungName',
@sequence_number =1 ;
GO
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'AccoungName',
@body = 'The database mail configuration was completed successfully.',
@subject = 'Automated Success Message';
GO
--Check the email event log
SELECT * FROM msdb.dbo.sysmail_event_log;
Quelle: https://www.sqlshack.com/configure-database-mail-sql-server/
|