Edit

sysmail_delete_principalprofile_sp (Transact-SQL)

  • Article

Applies to: SQL Server

Removes permission for a database user or role to use a public or private Database Mail profile.

Transact-SQL syntax conventions

Syntax

sysmail_delete_principalprofile_sp { [ @principal_id = ] principal_id | [ @principal_name = ] 'principal_name' } ,
    { [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' }
[ ; ]

Arguments

[ @principal_id = ] principal_id

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'

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

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'

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.

Return code values

0 (success) or 1 (failure).

Remarks

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 causes 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 isn't msdb.

Permissions

This stored procedure is owned by the db_owner role. You can grant EXECUTE permissions for any user, but these permissions may be overridden during a SQL Server upgrade.

Examples

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

EXEC msdb.dbo.sysmail_delete_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks Administrator';

Related content