In SQL Server, you can use the sysmail_delete_profileaccount_sp stored procedure to remove a Database Mail account from a profile.

You can remove the account from a specific profile, or you can remove it from all profiles. You can also remove all accounts from a given profile.

The way it works is, you provide two arguments; the account name or its ID, and the profile name or its ID. If you omit the argument for the profile, the account is removed from all profiles. If you omit the argument for the account, then all accounts are removed from the profile.

Remove a Specific Account from a Specific Profile

Here’s an example to demonstrate how to remove an account from a single profile.

First, let’s call sysmail_help_profileaccount_sp to see how many account/profile associations I have.

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

Result:

+--------------+-------------------+--------------+----------------+-------------------+
| profile_id   | profile_name      | account_id   | account_name   | sequence_number   |
|--------------+-------------------+--------------+----------------+-------------------|
| 3            | DB Admin Profile  | 3            | DB Admin       | 1                 |
| 2            | DB Public Profile | 2            | DB Public      | 1                 |
+--------------+-------------------+--------------+----------------+-------------------+

I have two associations. Let’s remove the first one. This is where the account called “DB Admin” belongs to the profile called “DB Admin Profile”).

Here’s how to remove them based on the account name and profile name.

EXEC msdb.dbo.sysmail_delete_profileaccount_sp
    @profile_name = 'DB Admin Profile',  
    @account_name = 'DB Admin';

The profile and account names are sysname with a default of NULL.

The IDs, if you choose to use them instead, are sysname with a default of NULL.

Now let’s call sysmail_help_profileaccount_sp again, to see how many associations we now have.

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

Result:

+--------------+-------------------+--------------+----------------+-------------------+
| profile_id   | profile_name      | account_id   | account_name   | sequence_number   |
|--------------+-------------------+--------------+----------------+-------------------|
| 2            | DB Public Profile | 2            | DB Public      | 1                 |
+--------------+-------------------+--------------+----------------+-------------------+

As expected, we now only have the second one.

Remove an Account from ALL Profiles

You can remove the account from all profiles by omitting the profile details. In other words, provide the account name or ID, but not the profile.

Like this:

EXEC msdb.dbo.sysmail_delete_profileaccount_sp 
    @account_name = 'DB Admin';

Remove ALL Accounts from a Profile

You can remove all accounts from a profile by omitting the account details. In other words, provide the profile name or ID, but not the account.

Like this:

EXEC msdb.dbo.sysmail_delete_profileaccount_sp 
    @profile_name = 'DB Admin Profile';

Location of the Stored Procedure

The sysmail_delete_profileaccount_sp stored procedure is located in the msdb database and its owner is dbo. Therefore, you’ll need to provide three part naming if msdb isn’t the current database.