Remove all non-default profiles + accounts from DBMail

removedbmailnondefault.sql

declare @defaultProfileId int;
select @defaultProfileId = pp.profile_id
from dbo.sysmail_principalprofile as pp
where pp.principal_sid = 0x00 /* Guest */
and pp.is_default = 1

if @defaultProfileId is null
begin
	;throw 50000, 'No default profile set', 1
	return
end

/* Remove every non-default profile 
	- Remove account-profile links
	- Then profile
*/
delete from dbo.sysmail_profileaccount where profile_id <> @defaultProfileId;
delete from dbo.sysmail_profile where profile_id <> @defaultProfileId;

/* Now remove any orphaned accounts */
with cte as (
	select a.account_id
	from dbo.sysmail_account as a
	where not exists (
		select *
		from dbo.sysmail_profileaccount as pa
		where pa.account_id = a.account_id
	)
)
delete from cte;