Transact SQL – Error – “Msg 14607 – Procedure msdb.dbo.sysmail_verify_principal_sp – principal name is not valid”

Background

Needing to deploy email notification across a couple of SQL Instances.

I am not into clicking through screen shots and so I am going to prepare a Transact SQL Script.

Error

Ran into an error

Image

error.Msg.14607.20190719.0133PM

Textual


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

Trouble Shooting

The error message states the principal is no good.

Command

The command that failed is :-


EXEC msdb.dbo.sysmail_add_principalprofile_sp 

      profile_name=@mailProfile

    , @principal_name=@principal

    , @is_default=0

Principal

Instance

Outline

Is Principal registered on the SQL Instance

SQL


declare @principal    sysname;
declare @principalSID varbinary(85);
declare @principalID  int;

set @principalSID = SUSER_SID(@principal)

set @principalID = SUSER_ID(@principal)

Database

Outline

Is Principal registered in msdb

SQL

use msdb;
declare @principal    sysname;
declare @userID       int;

set @userID = USER_ID(@principal)

Remediation

In our case, the principal did not have an account in msdb.

To satisfy that requirement, issued “CREATE USER [principal] from LOGIN [principal]“.

 

Reference

  1. Microsoft
    • sysmail_add_principalprofile_sp
      • Docs / SQL / Reference / System stored procedures / Database Mail / sysmail_add_principalprofile_sp
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s