Tag Archives: how to remove ssrs email address

How to update/replace or remove an email address from all SSRS subscriptions with T-SQL

Database Administrator, dba, SQL Server, SSRS, SSRS Reporting, T-SQL, tsql, , , , , , , , , , ,

Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.

To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.

If you’re worried about messing anything up then back up the table before running the script!

Create backup:

SELECT *
INTO [dbo].[Subscriptions_bk]
FROM [dbo].[Subscriptions]

Update/replace or remove an email address:

/*
To replace an email address with another email address:
Find and replace the following email addresses (Ctrl+H) with the new email address*/
/*
Email address to update/replace:
replaceSomeGuy@someCompany.com

Email address replacement:
newGuy@someCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'replaceSomeGuy@someCompany.com', 'newGuy@someCompany.com')
WHERE CHARINDEX('replaceSomeGuy@someCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

/*
To remove an email address:
NOTE:Run both of the following scripts as an email address may or may not end with ";"

Find and replace the following email address (Ctrl+H)
*/
/*
Email address to remove
removeSomeGuy@otherCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com;', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com;', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;


UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;