Cannot insert the value NULL into column ‘permission path’, table ‘@temp’; column does not allow nulls

Ekrem Önsoy

6 years ago

ERROR:

Msg 515, Level 16, State 2, Procedure sp_validate_user, Line 19
Cannot insert the value NULL into column ‘permission path’, table ‘@temp’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 136
profile name is not valid

EXPLANATION:

You may receive this error when trying to send an email via Database Mail with a code similar to the following.

Sample Code:

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'SMTP' 
,@recipients = 'ekrem.onsoy@test.com'
,@body = 'test_message'
,@subject = 'test'

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'SMTP'

,@recipients = 'ekrem.onsoy@test.com'

,@body = 'test_message'

,@subject = 'test'

There were two Database Mail Profile and Account in the SQL Server Instance.

SOLUTION:

The problem was that two Database Mail Profiles were not Public.

The Database Mail Profile named “SMTP” was not Public in the example above.

Either set the corresponding Database Mail Profile to Public or set the corresponding Login to use Private Database Mail Profile.

You can also make these settings through either T-SQL or SQL Server Management Studio.

To make the change from SSMS:

Object Explorer->Management->Database Mail->Manage Profile Security->Public Profiles\Private Profiles.