Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday 7 July 2015

To Send an DB Email from non-sysadmin user in SQL

Send an Email from non-sysadmin user in SQL


If user role have DB DatabaseMailUserRole  in the MSDB but still he cant send an Email through dbmail from databasemail  then u get 

SQL Database Mail Profile Name is Not Valid 

This problem occured when I was trying to send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 
For Example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile'
, @recipients = 'test@test.com'
, @subject = 'Automated Test Results (Successful)'
, @body = 'The stored procedure finished successfully.'
The error was:
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 141
Profile name is not valid
You can see that the non-sysadmin user has been added to the DatabaseMailUserRole role in the database.
Database Role Membership
 
Note: If the database mail profile is not set for public access then the user is not allowed to use the specified profile.
Now set the profile to Public.

Right-click on the Database Mail in the Management Studio and select the Configure Database Mail menu option.
Configure Database Mail in SQL
 
Click the "Next" button.
Database Mail Configure Wizard
 
Select "Manage profile security" then click the "Next" button.
Configuration Task in Database Mail
Now go to Public Profiles, select your profile name and then set the profile to Public then click the "Next" button .
Public Profiles in Database Mail

Note: The Manage Profile Security screen allows you to set this profile to either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (Microsoft database) to send e-mail using this profile.
Now click the "Finish" button.
Manage Profile Security in Database Mail
 
Now you can send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 2012.

No comments:

Post a Comment