sysmail_delete_profile_sp (Transact-SQL)

Deletes a mail profile used by Database Mail.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sysmail_delete_profile_sp  { [ @profile_id = ] profile_id | [    
        @profile_name = ] 'profile_name' } 
    [, [ @force_delete = ] force_delete ]

Arguments

  • [ @profile_id= ] profile_id
    Is the profile id of the profile to be deleted. profile_id is int, with a default of NULL. Either profile_id or profile_name must be specified.
  • [ @profile_name= ] 'profile_name'
    Is the name of the profile to be deleted. profile_name is sysname, with a default of NULL. Either profile_id or profile_name must be specified.
  • [ @force_delete= ] force_delete
    force_delete is bit. Default = 1. If force_delete equals 1, any e-mail messages that have a status of unsent or retrying will be updated to failed.

    If force_delete equals 0 and any e-mail messages have the status of unsent or retrying, an error will be raised, or, if there are no associated messages, the profile will be deleted.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

Deleting a profile does not delete the accounts used by the profile.

This stored procedure deletes the profile regardless of whether users have access to the profile. Use caution when removing the default private profile for a user or the default public profile for the msdb database. When no default profile is available, sp_send_dbmail requires the name of a profile as an argument. Therefore, removing a default profile may cause calls to sp_send_dbmail to fail. For more information, see sp_send_dbmail (Transact-SQL).

The stored procedure sysmail_delete_profile_sp is in the msdb database and is owned by the dbo schema. The procedure must be executed with a three-part name if the current database is not msdb.

Permissions

Execute permissions for this procedure default to members of the sysadmin fixed server role.

Examples

The following example shows deleting the profile named AdventureWorks Administrator. If there are unsent messages associated with this profile, an error will be raised and the profile will not be deleted.

EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_name = N'AdventureWorks Administrator' 
    ,@force_delete = 0;

See Also

Reference

Database Mail and SQL Mail Stored Procedures (Transact-SQL)

Other Resources

Database Mail
Database Mail Profiles
Select Configuration Task (Database Mail)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added the new parameter @force_delete to the Syntax, Arguments, and Example sections.