Partilhar via

sysmail_delete_principalprofile_sp (Transact-SQL)

  • Artigo

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

Topic link iconTransact-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 msdb database user or 0 for the public role. 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 msdb database user or 'public' for the public role. 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 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
Changed content:
  • Specified users as msdb users and that role is limited to the public role.