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
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!
WITH CTE_Email
AS
(
--SELECT recipients FROM MSDB.[dbo].[sysmail_allitems]
SELECT Email AS Recipients FROM
(values
('Martin.Croft@SQLServerSolutions.com'),
('Someone@MyWorksDomain.co.uk;SomeoneElse@MyWorksDomain.co.uk'),
('PersonalEmail@Yahoo.co.uk'),
('Evil@NastyHackers.co.uk'),
('Bill@Acceptable3rdParty.com;Helpdesk@Acceptable3rdParty.com')
) EmailAddress(Email)
)
SELECT DISTINCT
SUBSTRING(recipients,CHARINDEX('@', recipients)+1,LEN(recipients)) AS EmailDomains
FROM
(
SELECT
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS recipients
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(recipients,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS Email
FROM CTE_EMAIL
)t
CROSS APPLY Email.nodes('/XMLRoot/RowData')m(n)
)EmailAddress
Which results in the following
