April 22, 2020 at 5:27 pm
I was just given the unenviable task of configuring Database Mail to notify our team when sql agent is restarted. It went down recently and the sick, lame, and lazy (i.e. supervisors) were not delighted.
I successfully configured Database mail using the gui. I created a TSQL script to send an email to our team. Success.
I made sure my email account has rights in the public and private profiles in Database Mail and had msdb permissions to the DatabaseMailrole.
The enjoyment started when I tried to schedule my tsql script as a batch job. I set it to run when the agent is restarted and copied my script as a tsql job, I tried to run the script manually and it rejected the profile. I am running the script using my email account.
It has owner rights on the database and MSDB but is not sysadmin. I did not want to muck with the default sql agent service acct.
A potential problem: I setup the sql agent job using a sysadmin account , then changed ownership to my email account.
2nd potential problem I initially setup the public profile in databasemail using my sysadmin account. I changed the private profile to point to my email account, but I don't know how to do that using the public profile.
Thanks for your indulgence.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
April 23, 2020 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 24, 2020 at 5:27 am
This was removed by the editor as SPAM
April 24, 2020 at 8:14 am
So you have setup a mail profile and you are able to send a mail successfully?
Are you wanting to be alerted to the "Stop" or the "Start" of the service?
If stop you will want to write something at the operating system level to monitor the service on stop.
If start you can create a job and let it run on the scheduled of "when sql agent starts"
If you have done all that and it's not working please provide the scripts or screenshots or error messages etc of what is not working so the community can advise at what is potentially the issue.
April 28, 2020 at 12:10 am
It turned out I setup the email account correctly. The issue was making the profile public.
I found this code provided by microsoft. It did the job.
EXECUTE msdb.dbo.sysmail_add_profile_sp --create profile
@profile_name = 'SQL Server Profile',
@description = 'Email Profile'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp --join to account
@profile_name = 'SQL Server Profile',
@account_name = 'SQL Server Email Notification',
@sequence_number = '1'
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp --make public
@profile_name = 'SQL Server Profile',
@principal_name = 'public',
@is_default = 1?
Setting up the sql account is trivial if you know your smtp server
It's coffee time. Yeah me!
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply