0

Configuring Database Mail is so long.

So I want to create a script that automatically create it for me

DECLARE @YourEmail NVARCHAR(50) SET @YourEmail = 'MyEmail@mydomain.com' --Put here your E-mail
DECLARE @YourPassword NVARCHAR(50) SET @YourPassword = 'MyPassword' --Put here your E-mail
DECLARE @YourSMTPSserver NVARCHAR(50) SET @YourSMTPSserver = 'MySMTPserver' --Put here your SMTP Server
DECLARE @YourPort int SET @YourPort = '25' --Put here your SMTP port

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'SQL_Spring_Cleaning_Account',  
    @description = 'Mail account for SQL_Spring_Cleaning.',  
    @email_address = @YourEmail,  
    @replyto_address = @YourEmail,
    @display_name = 'SQL_Spring_Cleaning Mailer',  
    @mailserver_name = @YourSMTPSserver, 
    @port = @YourPort,
    @use_default_credentials = 0,
    @username = @YourEmail,
    @password = @YourPassword;


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

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

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'SQL_Spring_Cleaning_Profile',  
    @principal_name = 'ASPIRES3\franc',  
    @is_default = 0 ; 

But unfortunately SSMS returns me:

Msg 14607, Level 16, State 1, Procedure sysmail_verify_principal_sp, Line 49 [Batch Start Line 0]
principal name is not valid

The error is obviously on line

@principal_name = 'ASPIRES3\franc', 

but I don't understand what is wrong with that

1

Is the name you're using a user in the msdb database?

[ @principal_name = ] 'principal_name' The name of the database user or role in the msdb database for the association. principal_name is sysname, with a default of NULL. Either principal_id or principal_name must be specified. A principal_name of 'public' makes this profile a public profile, granting access to all principals in the database.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.