[SqlException (0x80131904): EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.]
As I attempted to solve for this error, I ended up using EXECUTE AS in the trigger. When attempting to use this method, I received the following error;
[SqlException (0x80131904): Cannot execute as the server principal because the principal "SQLMail" does not exist, this type of principal cannot be impersonated, or you do not have permission.]
The I decieded to trun away from all the post that I was following on the internet and start from scratch in a test enviorment. Here’s what I did:
As dbo, create the database Principal that will use Test database to send the email.
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password'
GO
Create the database Principal that will use msdb database to send the email.
USE [master]
GO
CREATE LOGIN [MailUser] WITH PASSWORD=N'password'
GO
EXEC master..sp_addsrvrolemember @loginame = N'Mail_User', @rolename = N'sysadmin'
GO
Now start setting up the mail. To send Database Mail using stored procedures, you must be a member of the DatabaseMailUserRole database role in the msdb database.
USE [msdb]
GO
CREATE USER [MailUser] FOR LOGIN [MailUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'MailUser'
GO
Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.
To list all other members of the DatabaseMailUserRole execute the following statement:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
Go to Surface Area configuration for features and expand Database engine option and select DatabaseMail option and checked Enable Database mail Stored procedure and click OK or Apply button.
Create a new Database Mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test_Database_Mail',
@description = 'Mail account for testing sending email.',
@email_address = 'somebody@domain.com',
@replyto_address = 'somebody@domain.com',
@display_name = 'Clay McDonald',
@mailserver_name = 'mail.domian.com'
Create a Database Mail profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Test_Email_Profile',
@description = 'Profile for testing sending email.'
Add the account to the profile.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Test_Email_Profile',
@account_name = 'Test_Database_Mail',
@sequence_number = 1
Grant access to the profile to all users in the msdb database.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test_Email_Profile',
@principal_name = 'public',
@is_default = 1
Check to see if Service Broker is enabled.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
is_broker_enabled will be 1 if Service Broker is enabled for the given database, otherwise it'll be 0.
To enable Service Broker run the following;
ALTER DATABASE msdb
SET ENABLE_BROKER;
Now test that Database Mail is working.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test_Email_Profile',
@recipients='somebody@domain.com',
@body = 'This is a test for Database Mail.',
@body_format = 'TEXT',
@subject = 'Database Mail Test'
-- eMail sent!
Now I create my test database.
USE [master]
GO
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'D:\MSSQL\DATA\Test.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'D:\MSSQL\LOG\Test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Test', @new_cmptlevel=90
GO
Then create test table that will fire the trigger.
USE [Test]
GO
CREATE TABLE [dbo].[NewMail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Some_Text] [varchar](max) NULL
)
GO
Create the trigger.
USE Test
GO
CREATE TRIGGER TestMailTrigger
ON dbo.NewMail
AFTER INSERT
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test_Email_Profile',
@recipients='somebody@domain.com',
@body = 'This is a test for Database Mail.',
@body_format = 'TEXT',
@subject = 'Database Mail Test'
END
GO
Test the trigger.
USE Test
GO
INSERT INTO dbo.NewMail (Some_Text)
VALUES('Fire the trigger to send mail')
GO
-- Mail sent!
Now we test logging in as TestUser. First, add TestUser to the Test database.
USE [Test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Test]
GO
EXEC sp_addrolemember N'db_datareader', N'TestUser'
GO
USE [Test]
GO
EXEC sp_addrolemember N'db_datawriter', N'TestUser'
GO
Now, disconnect and reconnect as Test_User.
USE Test
GO
INSERT INTO dbo.NewMail (Some_Text)
VALUES('Fire the trigger to send mail')
GO
-- It works!
NOTE: Database Mail messages and their attachments are stored in the msdb database. Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention program. Use the sysmail_delete_mailitems_sp stored procedure to permanently delete e-mail messages from the Database Mail tables. An optional argument allows you to delete only older e-mails by providing a date and time. E-mails older than that argument will be deleted. Another optional argument allows you to delete only e-mails of a certain type, specified as the sent_status argument. You must provide an argument either for @sent_before or @sent_status. To delete all messages, use @sent_before = getdate().
If you have permission issues, you can start over by deleting Database Mail Accounts.
In SQL Mgt Studio, under Management -> Database Mail, you can select “Manage Database Mail accounts and profile”. Here you’ll find options for managing accounts and profiles.
I stopped all SQL Services (SSIS, FullText, Analysis Services, Reporting, SQL Server Browser, and SQL Agent) and restarted SQL Server (MSSQLSERVER).
Next, I added ADMIN: and the name of my SQL Server Instance.
When I clicked Connect, I was connected to the instance. You can also connect with sqlcmd: