sysmail_delete_principalprofile_sp (Transact-SQL)

  • Article

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

Topic link icon 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
    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.

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 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.

Permissions

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

Examples

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

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

See Also

Reference

Database Mail Stored Procedures (Transact-SQL)

Concepts

Database Mail

Database Mail Configuration Objects