sysmail_delete_principalprofile_sp (Transact-SQL)

[ @principal_id = ] principal_id

Is the ID of the database user or role in the msdb database for the association to delete. principal_id is int, with a default of NULL. To make a public profile into a private profile, provide the principal ID 0 or the principal name 'public'. Either principal_id or principal_name must be specified.

[ @principal_name = ] 'principal_name'

Is the name of the database user or role in the msdb database for the association to delete. principal_name is sysname, with a default of NULL. To make a public profile into a private profile, provide the principal ID 0 or the principal name 'public'. Either principal_id or principal_name must be specified.

[ @profile_id = ] profile_id

Is the ID of the profile for the association to delete. 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 for the association to delete. profile_name is sysname, with a default of NULL. Either profile_id or profile_name must be specified.

0 (success) or 1 (failure)

To make a public profile into a private profile, provide 'public' for the principal name or 0 for the principal id.

Use caution when removing permissions for the default private profile for a user or the default public profile. 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_principalprofile_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.

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

The following example shows deleting the association between the profile AdventureWorks2008R2 Administrator and the login ApplicationUser in the msdb database.

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks2008R2 Administrator' ;

Reference

Concepts