Email Security SQL Server

One security vulnerability that people don’t often realise , is that SQL server can email information out through the likes of  sp_send_dbmail. It goes without saying that you should take steps to stop emails externally and then if its a requirement ensure that its to approved email domain only.

The following bit of code shows the domains that emails have actually been emailed to, the code below shows several email addresses ( mock up of  MSDB.dbo.sysmail_allitems email addresses.

If you notice there are multiple emails on one line

Email Address Example

The following code will strip out all the email addresses individually and remove the Email address to just leave the domain name. So quickly you can check if you are getting emails sent places they shouldn’t be. You can even add in your companies email address so it turns into an exception report!

--SELECT recipients FROM MSDB.[dbo].[sysmail_allitems]
SELECT Email AS Recipients FROM
) EmailAddress(Email)


SUBSTRING(recipients,CHARINDEX('@', recipients)+1,LEN(recipients)) AS EmailDomains
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS recipients
SELECT CAST('<XMLRoot><RowData>' + REPLACE(recipients,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS Email
CROSS APPLY Email.nodes('/XMLRoot/RowData')m(n)

Which results in the following

Email Address Domains

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s