Prevent users from sending an email attachment in sql server

Question

In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment.

For example, someone import data into excel file and send it via mail using the sp_send_dbmail procedure.

Can we restrict use of this procedure?

Or is there any other way to do it?

There are some users that have sysadmin permission, I want to restrict them too.

If it is not possible, at least I want to get notification when someone sends an email with attachment.

Trigger or procedure might work, but I could not get any result.

Answer 1

I couldn't find a way to prevent it, but I created a trigger to send an email when someone sends attachment. It works fine for me.

CREATE OR ALTER TRIGGER trg_attachment_notification
ON msdb..sysmail_attachments
AFTER INSERT
AS
BEGIN
DECLARE @AttachmentFileName NVARCHAR(MAX);
DECLARE @SentByUser NVARCHAR(MAX);
DECLARE @SentDate DATETIME;
DECLARE @EmailSubject NVARCHAR(MAX);
DECLARE @EmailBody NVARCHAR(MAX);
DECLARE @Recipients NVARCHAR(MAX);
DECLARE @Attachment_EmailSubject NVARCHAR(MAX);
DECLARE @Attachment_EmailBody NVARCHAR(MAX);
SELECT 
@AttachmentFileName = a.filename, 
@Recipients = i.recipients, 
@SentByUser = i.send_request_user, 
@Attachment_EmailSubject = i.subject, 
@Attachment_EmailBody = i.body, 
@SentDate = a.last_mod_date
FROM sysmail_attachments a 
JOIN sysmail_mailitems i 
ON a.mailitem_id=i.mailitem_id
ORDER BY sent_date DESC;
SET @EmailSubject = 'Email Attachment has been sent! ';
SET @EmailBody = 'A new email attachment has been sent:' + CHAR(13) + CHAR(10) +
 'Email Subject: ' + @Attachment_EmailSubject + CHAR(13) + CHAR(10)+
 'Email Body: ' + @Attachment_EmailBody + CHAR(13) + CHAR(10) +
 'Filename: ' + @AttachmentFileName + CHAR(13) + CHAR(10) +
 'Sent by: ' + @SentByUser + CHAR(13) + CHAR(10) +
 'Recipients: ' + @Recipients + CHAR(13) + CHAR(10) +
 'Sent Date: ' + CONVERT(NVARCHAR(MAX), @SentDate, 120);
IF @SentByUser <> 'xxx'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'db_mail', 
 @recipients = '[email protected]', 
 @subject = @EmailSubject,
 @body = @EmailBody;
END;
END;

Answer 2

you cannot prevent, specially sysadmins to use the sp_send_dbmail procedure with or without attachments

but you can see who did what and when, and if there were attachments or not.

check this out

select *
from msdb.dbo.sysmail_mailitems l
WHERE l.sent_date > DATEADD(DAY, -1,GETDATE())
order by mailitem_id desc

and also check these other 2 queries here:

SELECT items.subject ,
 items.recipients ,
 items.copy_recipients ,
 items.blind_copy_recipients ,
 items.last_mod_date ,
 l.description
FROM msdb.dbo.sysmail_faileditems AS items
 LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l 
 ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > DATEADD(DAY, -1,GETDATE())
SELECT l.*
FROM msdb.dbo.sysmail_event_log AS l 
WHERE l.log_date > DATEADD(DAY, -1,GETDATE())
order by log_id desc

for example, you could set up a job to run every hour, that executes this first query and email you the results, in an attachment.

enter image description here