SQL: Sending email to an operator rather than a recipient in SQL Server

I was answering a question on a forum the other day, and the person asking the question was puzzled about how to send an email to an operator (i.e. msdb operator) rather than to just any other email address.

The reason he wanted to do that was that he wanted to use the Agent operator system to define a name. He didn't want to hard-code a recipient name in his code. By having it as an operator, it could be changed later, independent of the code.

Doing that isn't so hard. You just need to retrieve the operator's email address from msdb.dbo.sysoperators before sending the mail. Here's an example:

And then send the email like this:

And as BWoodburn noted in the comments, there is also a built-in function that you might be able to call:

sp_notify_operator

It offers slightly different options.

I hope that one of these helps someone.

8 thoughts on “SQL: Sending email to an operator rather than a recipient in SQL Server”

    1. Have to admit that I haven't used that option in a very, very long time. There also used to be a mechanism where you could email a query to SQL Server, it would execute it, and email back the results. Long gone now though.

      1. I ask you for grapes and you tell me about wine I cannot have!!!!!
        lol thank you I'll have to have a look. I got 1 DEV that had sa a few years back and now we are trying to clean up old profiles……such fun ..all day its outlook and dbmail *smh*

        1. I just checked, and it looks like the @query parameter still works. When you use it, it logs on, runs the query, and sends the results of the query in the email. The results can be sent as an attachment or included in the body of the email. The catch is the authentication. The limitation says:

          When using @query parameter, user that executes sp_send_dbmail must be SQL login or directly mapped to principal (login) of Azure AD or AD. If user is member of Azure AD group or AD group it will not be able to execute the query. This is due to Azure SQL Managed Instance impersonation and EXECUTE AS limitations.

          We'd normally do this via group membership, not via a direct SQL login. (We try to eliminate direct individual SQL logins). So that wouldn't work for us, but depending upon your situation, it might work. They removed the "listen to emails in an inbox" part, but left the "send the results via email" part.

Leave a Reply

Your email address will not be published. Required fields are marked *