Cleaning up database mail profiles and accounts
April 24, 2018
You know that dev server you’ve got lying around that has about half a dozen database mail profiles on? Maybe one account for every provider you’ve tested? Or maybe it’s even a production server that has had its profile faithfully updated to a new account each time your SMTP server moves but never had the old ones cleared out?
After recently moving all of our servers to use SendGrid SMTP for sending out database mail I decided to perform some long overdue spring cleaning. In our case that included a couple of dev servers, as well as a production server from which you could divine the history of the company from the various database mail profiles and accounts it had.
Your servers might have a legitimate reason to contain multiple mail profiles/accounts, so only run the below script on a server if you are sure you want to remove every account/profile except the default.
declare @defaultProfileId int; select @defaultProfileId = pp.profile_id from dbo.sysmail_principalprofile as pp where pp.principal_sid = 0x0 /* Guest */ and pp.is_default = 1; if @defaultProfileId is null begin ;throw 50000, 'No default profile set', 1; return; end /* Delete non-default profiles and their account mappings */ delete from dbo.sysmail_profileaccount where profile_id <> @defaultProfileId; delete from dbo.sysmail_profile where profile_id <> @defaultProfileId; /* Remove 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;