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.