CODEWITHSUNDEEP
sqlsql-server
Eric Klaus
Eric Klaus

Reputation: 955

Sending an email via SQL Server causes error

I'm trying to send an e-mail via SQL Server Management Studio, but it returns an error.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Adventure Works Administrator',
     @recipients = '[email protected]',
     @body = 'The stored procedure finished successfully.',
     @subject = 'Automated Success Message'

Error is:

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I've surfed the internet to fix this problem, but none worked out. Need your help. Thanks

Upvotes: 1

Views: 469

Answers (1)

cubitouch
cubitouch

Reputation: 1937

Maybe your user do not have correct rights on the SQL instance you are targeting, as suggested in the comments :

grant execute on dbo.sp_send_dbmail to <usernamehere>;

See the Troubleshooting article on this issue :

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = '<user or role name>';
GO

Also, you may have not configured this feature, see documentation :

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure.

And this one for configuration :

USE master
Go

EXEC sp_configure 'show advanced options', 1
Go

RECONFIGURE
Go

EXEC sp_configure 'Database Mail XPs', 1
Go

RECONFIGURE
Go

EXEC sp_configure 'show advanced options', 0
Go

RECONFIGURE
Go

Upvotes: 1

Related Questions