Database mail permission problem

Question

Database mail permission problem

Sagar Sawant

SSC Eights!

Points: 853
More actions
November 7, 2011 at 11:13 pm

#251200

Hi guys,
I am getting error when trying to execute database profile and database account through stored procedure.
I already executed,
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
But getting below errors when executing stored procedure,
The SELECT permission was denied on the object 'sysmail_profile', database 'msdb', schema 'dbo'. The SELECT permission was denied on the object 'sysmail_account', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_profile_sp', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_account_sp', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_profileaccount_sp', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_principalprofile_sp', database 'msdb', schema 'dbo'.
Any help would be greatly appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Sagar Sawant (11/7/2011)
Hi guys,
I am getting error when trying to execute database profile and database account through stored procedure.
I already executed,
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
But getting below errors when executing stored procedure,
The SELECT permission was denied on the object 'sysmail_profile', database 'msdb', schema 'dbo'. The SELECT permission was denied on the object 'sysmail_account', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_profile_sp', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_account_sp', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_profileaccount_sp', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'sysmail_add_principalprofile_sp', database 'msdb', schema 'dbo'.
Any help would be greatly appreciated.

the person doing the EXECUTE on the stored procedure must be either a user or in a role that is also in the msdb database.

that role is built in, and named "DatabaseMailUserRole"

assuming windows credentials, you want to add a group from windows to the MSDB database, ie MyDomain\AuthenticatedUsers, to the msdb database and then add that group to the DatabaseMailUserRole role.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Jonathan Kehayias describes a different method here:

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail[/url]

Hope this helps

Gianluca

-- Gianluca Sartori